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.

12 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. PostgreSQL NULL value

    ReplyDelete
  3. شرکت تلکا هاست پیشرو در میزبانی انواع وب سایت و هم چنین دارای انواع هاست فوق ارزان با کنترل پنل سی پنل میباشد.حتما از سایت ما دیدن کنید و از قیمت های مناسب برای انواع سرویس های وب و ثبت انواع دامنه شگفت زده شوید.

    ReplyDelete