Thread: how to reset the sequences of SERIAL vars?
Hello, its a FAQ i know, but i couldn't find it on the site or the docu... this problem arises mainly after a backup was played in again.... when inserting from backup the tables are filled with all fields, this means alos those defined as serial, but without using nextval... this means that the sequences for those vars are out of synch with the table after the backup... now there was a sequence to set this up and runnign again, but i cna't find it in my papers anymore, so if someone could kindly point me out on how to set up the correct values un the sequence i will be really grateful. -- ciao bboett ============================================================== bboett@adlp.org http://inforezo.u-strasbg.fr/~bboett ===============================================================
On Fri, Nov 01, 2002 at 10:59:11AM +0100, Bruno Boettcher wrote: > Hello, > its a FAQ i know, but i couldn't find it on the site or the docu... > this problem arises mainly after a backup was played in again.... > > when inserting from backup the tables are filled with all fields, this > means alos those defined as serial, but without using nextval... > > this means that the sequences for those vars are out of synch with the > table after the backup... > > now there was a sequence to set this up and runnign again, but i cna't > find it in my papers anymore, so if someone could kindly point me out > on how to set up the correct values un the sequence i will be really > grateful. The way to change a sequence is to use setval. If you're restoring from a backup, make sure that when you make the backup using pg_dump you also dump the sequence. This will produce the appropriate setval() command. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Attachment
On 1 Nov 2002 at 10:59, Bruno Boettcher wrote:
> when inserting from backup the tables are filled with all fields, this
> means alos those defined as serial, but without using nextval...
>
> this means that the sequences for those vars are out of synch with the
> table after the backup...
>
> now there was a sequence to set this up and runnign again, but i cna't
> find it in my papers anymore, so if someone could kindly point me out
> on how to set up the correct values un the sequence i will be really
> grateful.
From createsequence.html in postgresql manual
---------------------------------------
Update the sequence value after a COPY FROM:
BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', max(id)) FROM distributors;
END;
---------------------------------------
Also look at functions-sequence.html for more reference.
HTH
Bye
Shridhar
--
Barometer, n.: An ingenious instrument which indicates what kind of weather we
are having. -- Ambrose Bierce, "The Devil's Dictionary"