Monday, 22 August 2016

PostgreSQL setval Sequence - PostgreSQL manually alter sequence - Reset sequence of setval in PostgreSQL - Ali Raza Bhayani


In one of the my PostgreSQL tables, I used a very handy builtin Sequence Manipulation function setval() of PostgreSQL to generate Primary Keys in a sequential and controlled manner. But during a migration, I wanted to identify new records by Primary Key sequence greater than thousand. A very handy and tested recipe to restart the setval() function sequence for such cases is by using RESTART WITH in the following way:
ALTER SEQUENCE seq RESTART WITH 1000;
is equivalent to:
    SELECT setval('sequence_name', 1, FALSE);
Either of the above statements may be used to restart the sequence and for getting the next value following statement can be used:
    nextval('sequence_name')
The above recipe can also be used to reset PostgreSQL primary key sequence when it falls out of sync.

No comments:

Post a Comment