[GENERAL] RV: Serial fields

[Pablo Sentis]

-----Mensaje original----- De: Pablo Sentis <[EMAIL PROTECTED]> Para: PostgreSQL mailing list <pgsql-general@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