RV: Serial fields
| От | Pablo Sentis |
|---|---|
| Тема | RV: Serial fields |
| Дата | |
| Msg-id | 000e01beeafa$151565c0$5a020164@PLSENTIS обсуждение исходный текст |
| Ответы |
Re: [GENERAL] RV: Serial fields
("Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>)
|
| Список | pgsql-general |
-----Mensaje original-----
De: Pablo Sentis <grupoitem@ctv.es>
Para: PostgreSQL mailing list <pgsql-general@postgreSQL>
Fecha: viernes, 20 de agosto de 1999 11:59
Asunto: Serial fields
De: Pablo Sentis <grupoitem@ctv.es>
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
В списке pgsql-general по дате отправления: