Help: a rare problem with backing and restoring - Mailing list pgsql-general

From bombadil@wanadoo.es
Subject Help: a rare problem with backing and restoring
Date
Msg-id 20020418125016.GA8989@fangorn
Whole thread Raw
Responses Re: Help: a rare problem with backing and restoring
List 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

pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: freebsd install
Next
From: "Nigel J. Andrews"
Date:
Subject: Re: freebsd install