Thread: Problem with serial counters
When I install my application, I use a SQL script to create the different tables.
Most of my tables start with a Serial column called RowId.
In my setup script I insert a few records into some tables, providing all fields, including the RowId (1, 2, 3, etc).
The problem I have is that when my user tries to insert a new record without the RowId field,
Postgres complains that it finds a duplicate key.
I guess when I do an insert with all the fields (including the RowId), Postgres does not increment the serial counter.
I cannot remove the RowId field from the Insert of my setup script because this setup script is also used by customers using MySQL.
What can I do to force PostGres to update the internat serial counter when I do an insert with a specified value for the serial?
On Feb 25, 2011, at 2:39 AM, Jacques Lebrun wrote: > What can I do to force PostGres to update the internat serial counter when I do an insert with a specified value for theserial? > After inserting the all the Data, use ALTER SEQUENCE Command: http://www.postgresql.org/docs/8.4/static/sql-altersequence.html Thanks & Regards, Vibhor
On 24/02/11 21:09, Jacques Lebrun wrote: > I guess when I do an insert with all the fields (including the RowId), > Postgres does not increment the serial counter. > I cannot remove the RowId field from the Insert of my setup script > because this setup script is also used by customers using MySQL. Apart from Vibhor's suggestion (which is the typical way PG does it) you can also set the auto field to DEFAULT: INSERT INTO mytable (rowid, other) VALUES (DEFAULT,'a'), (DEFAULT,'b'); This should work on either system. -- Richard Huxton Archonet Ltd