Postgres Pro
Downloads
Home   >   mailing lists

RV: Serial fields - Mailing list pgsql-general

From Pablo Sentis
Subject RV: Serial fields
Date
Msg-id 000e01beeafa$151565c0$5a020164@PLSENTIS
Whole thread Raw
Responses Re: [GENERAL] RV: Serial fields
List pgsql-general
Tree view
 
-----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

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 by date:

Previous
From: Daniel Péder
Date:
Subject: subscribe
Next
From: "F J Cuberos"
Date:
Subject: Problem accesing CVS tree