9

PostgreSQL set Next ID Sequence Value to MAX(id) from Table

 2 years ago
source link: https://gist.github.com/henriquemenezes/962829815e8e7875f5f4376133b2f209
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Instantly share code, notes, and snippets.

PostgreSQL set Next ID Sequence Value to MAX(id) from Table

Last one should be:

SELECT setval('table_id_seq', (SELECT MAX(id) FROM table)+1);

quantizer commented on Jun 14, 2018 •

edited

@Dev-Dipesh due to docs we don't need +1.

SELECT setval('foo', 42);           Next nextval will return 43
SELECT setval('foo', 42, true);     Same as above
SELECT setval('foo', 42, false);    Next nextval will return 42

Thanks!

6high commented on Nov 6, 2019

thanks

If sequence contains uppercase letters in name:
SELECT setval('"Foo"', 42);

Notice the double quotes inside the single quotes

Thanks!

Hello, it didn't fit me (if the table is empty, if I need to reset to the first value). I've made my own procedure based on it, maybe it can be helpful for somebody. https://urpylka.com/posts/post-15/

Thank you

zouchao commented on Apr 8 

select setval('table_id_seq', max(id)) from table_name;  -- Next nextval will return  max(id) + 1

If sequence contains uppercase letters in name: SELECT setval('"Foo"', 42);

Notice the double quotes inside the single quotes

Thank you very much, that was really useful ! Spent hours... lol

liaden commented on Jun 9

See https://wiki.postgresql.org/wiki/Fixing_Sequences to fix all sequences in one go

Not always an issue, but worth noting: the value that it sets it to if there are no rows is 1 which results in the first invocation of nextval returning 2 and skipping 1.

Thanks !

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK