Help: a rare problem with backing and restoring

From: bombadil(at)wanadoo(dot)es
To: Lista PostgreSql <pgsql-general(at)postgresql(dot)org>
Subject: Help: a rare problem with backing and restoring
Date: 2002-04-18 12:50:16
Message-ID: 20020418125016.GA8989@fangorn
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all.

I am desesperated, because I want to incorporate old data in a new
database schema with referential integrity rules.

In order to get this I do:

pg_dump -aD -f data.sql database

Then i have a file with a collection of inserts. Drop old database
and make new. Both are equal, except in referential integrity rules.

And next... the problem.

Look at this tables:

----------------------------------------------
create table aviso (
número serial primary key,
fecha date default CURRENT_DATE,
hora time default CURRENT_TIME,
usuario int4 references empleado(cod),
procedencia int4 references procedencia,
línea int4 references línea,
empresa int4 references empresa,
urgente bool default 'f',
estado int4 references estado,
fecha_terminado date,
orden int4,
detalle text
);
create index avi_fecha_ndx on aviso (fecha);
create index avi_hora_ndx on aviso (hora);
create index avi_usuario_ndx on aviso (usuario);
create index avi_procedencia_ndx on aviso (procedencia);
create index avi_linea_ndx on aviso (línea);
create index avi_empresa_ndx on aviso (empresa);
create index avi_urgente_ndx on aviso (urgente);
create unique index avi_orden_ndx on aviso (orden);
create index avi_estado_ndx on aviso (estado);
create index avi_fecha_aceptado_ndx on aviso (fecha_aceptado);
create index avi_fecha_terminado_ndx on aviso (fecha_terminado);

-----------------------------------
create table técnico_aviso (
cod serial primary key,
aviso int4 not null references aviso(número) on delete cascade,
empleado int4 not null references empleado(cod),
fecha date not null,
hora time not null,
duración interval,
comentario text
);
create unique index tec_aviso_unico_ndx on técnico_aviso (aviso, empleado, fecha, hora);
create index tec_avi_hora_ndx on técnico_aviso (hora);
create index tec_avi_empleado_ndx on técnico_aviso (empleado);
create index tec_avi_fecha_ndx on técnico_aviso (fecha);

---------------------------------

A little example of all my database.

técnico_aviso(aviso) references aviso(número). In file data.sql I
have lines of type:

INSERT INTO "técnico_aviso"
("cod","aviso","empleado","fecha","hora","duración","comentario")
VALUES (1,0,17,'20/09/2000','17:00:00',NULL,NULL);

INSERT INTO "técnico_aviso"
("cod","aviso","empleado","fecha","hora","duración","comentario")
VALUES (2,5,8,'05/07/1999','17:00:00',NULL,NULL);

INSERT INTO "técnico_aviso"
("cod","aviso","empleado","fecha","hora","duración","comentario")
VALUES (3,6,16,'29/06/1999','20:34:00',NULL,NULL);

First line is illegal for referential integrity because of
técnico_aviso[aviso] = 0 (aviso[número] = 0 doesn't exist),
but the other two are perfectly legal. If I insert then from psql, I
get no error. But when I execute data.sql script none of them are
inserted in técnico_aviso...

I don't understand this situation. What can be the problem?. It have
no sense and I am going crazy 8P

Please, a light?... I use Postgresql 7.2.1 in Debian unestable (Sid).

Thanks.

David

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tycho Fruru 2002-04-18 12:53:26 Re: Postgresql installation problem
Previous Message Nigel J. Andrews 2002-04-18 12:46:09 Re: freebsd install