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 sequence_name RESTART WITH 1000;
is equivalent to:
    SELECT setval('sequence_name', 1000, FALSE);
Either of the statements may be used to restart the sequence and you can get the next value by:
    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