Thread: Use of Serial type
Using php, I create a table with a SERIAL field. In the same script, the last thing I do is a COPY ... FROM ... USING DELIMITERS...to load data I have outputted from another system into a text delimited file. I didn't get the autoincrementing of the serial field to work, so I modified my output-from-another-system program to "manually" set the serial field value. That worked. However, it does not seem to have done a default SETVAL. Do I do a "manual" SETVAL of 1 greater than the highest value from the input text file, or am I missing something in the create and copy process? Jim Beckstrom
on 9/18/02 12:44 PM, jrbeckstrom@voyager.net purportedly said: > Using php, I create a table with a SERIAL field. In the same > script, the last thing I do is a COPY ... FROM ... USING DELIMITERS...to > load data I have outputted from another system into a text delimited > file. I didn't get the autoincrementing of the serial field to work, so > I modified my output-from-another-system program to "manually" set the > serial field value. That worked. However, it does not seem to have > done a default SETVAL. Do I do a "manual" SETVAL of 1 greater than the > highest value from the input text file, or am I missing something in the > create and copy process? I am not certain about the behavior of COPY, but with an INSERT the serial trigger will not click if you are inserting a value into the serial field. Nor will it be updated. This behavior may have changed since I last experienced it, but that's my recollection. This may be the same for COPY. So you probably have to manually update the serial counter. Keary Suska Esoteritech, Inc. "Leveraging Open Source for a better Internet"
2002. szeptember 18. 20:44 dátummal Jim Beckstrom ezt írta: | Using php, I create a table with a SERIAL field. In the same | script, the last thing I do is a COPY ... FROM ... USING | DELIMITERS...to load data I have outputted from another system into a | text delimited file. I didn't get the autoincrementing of the serial | field to work, so I modified my output-from-another-system program to | "manually" set the serial field value. That worked. However, it | does not seem to have done a default SETVAL. Do I do a "manual" | SETVAL of 1 greater than the highest value from the input text file, | or am I missing something in the create and copy process? | | Jim Beckstrom COPY is just a SQL-like shorthand for dumping and restoring database tables, so it's not the convenient way to insert data as regular with autoincrementing columns of type serial. You should also consider about what would happen in concurrent usage if you merely use "max+1". It may be better to use setval('seq', currval('seq')+1); But, AFAIR, Tom Lane wrote me that this solution can be also crashed in concurrent usage. If you're interested in, please, search the pgsql-general archives. -- Papp, Győző - pgerzson@freestart.hu
Jim, > Using php, I create a table with a SERIAL field. In the same > script, the last thing I do is a COPY ... FROM ... USING DELIMITERS...to > load data I have outputted from another system into a text delimited > file. I didn't get the autoincrementing of the serial field to work, so > I modified my output-from-another-system program to "manually" set the > serial field value. That worked. However, it does not seem to have > done a default SETVAL. Do I do a "manual" SETVAL of 1 greater than the > highest value from the input text file, or am I missing something in the > create and copy process? That sounds fine. However, I would bundle everything in a transaction to be safe. Otherwise, you risk having a user access the table without the serial value correctly set. -- -Josh Berkus Aglio Database Solutions San Francisco