Обсуждение: 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.

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"