RV: Serial fields

-----Mensaje original-----
De: Pablo Sentis <grupoitem(at)ctv(dot)es>
Para: PostgreSQL mailing list <pgsql-general(at)postgreSQL>
Fecha: viernes, 20 de agosto de 1999 11:59
Asunto: Serial fields

Hi All!

Working with PostgreSQL 6.5.1 on Intel platf.


I´m trying to migrate an Acces database to postgress . First of all I´ve created the database structure in the Postgres machine with SQL table creation statements and this works properly . But the problem comes when I try to transfer the data via a flat file : If I try to do (from a Windows program) the data transfer when the table has a serial field , even though the original data is written in the postgres table all subsequent INSERTS from psql get a 'Duplicate index' error . In the exported flat file included the original serial values :

MDB TEXT FILE POSTG table
==== ======= ========

1, NAME1 1, NAME1 1, NAME1
2, NAME2 2, NAME2 2, NAME2
3, NAME3 3, NAME3 3, NAME3
5, NAME5 5, NAME5 5, NAME5

After this if I try an INSERT : INSERT INTO table (NAME) values ('NAME6') I get the error

Of course I know I should not write on a read-only field so as
I need to import the original serial values as they are referrenced in other tables in the database

If I don´t do this I´ll get:

MDB TEXT FILE POSTG table
==== ======= ========

1, NAME1 NAME1 1, NAME1
2, NAME2 NAME2 2, NAME2
3, NAME3 NAME3 3, NAME3
5, NAME5 NAME5 4, NAME5

losing all integrity.

A partial solution was to export into the flat file an empty line for every deleted record:

MDB TEXT FILE POSTG table
==== ======= ========

1, NAME1 NAME1 1, NAME1
2, NAME2 NAME2 2, NAME2
3, NAME3 NAME3 3, NAME3
--------------- FOO 4, FOO
5, NAME5 NAME5 5, NAME5


Deleting after transfer all records where name = 'FOO' , so I get

Data in Acces Data in
MDB POSTG table
==== ========

1, NAME1 1, NAME1
2, NAME2 2, NAME2
3, NAME3 3, NAME3
5, NAME5 5, NAME5

as I wanted.

A now the question is: How can I do the same procedure with the COPY FROM SQL statement? . Having 100.000 registers is really expensive to do all the INSERT statements . I don´t know how to build the flat file as I always get an error as the COPY FROM procedure seems to expect the serial field too . A parser error apears 'around NAME1' and crashes ...
I don´t if I have been able to explain myself (as I´m writting from Spain and my English is still pathetic) but any help will be welcome,

Regards from a sunny and almost boiling Alicante , Spain

Pablo