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

postgresql-set-id-seq.sql

-- Get Max ID from table
SELECT MAX(id) FROM table;

-- Get Next ID from table
SELECT nextval('table_id_seq');

-- Set Next ID Value to MAX ID
SELECT setval('table_id_seq', (SELECT MAX(id) FROM table));

Last one should be:

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

@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

@Dev-Dipesh it worked... thanks

@Dev-Dipesh thanks.

Thanks!

thanks

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

Notice the double quotes inside the single quotes

Thanks!

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

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

Thanks for this.

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/

Thanks!

Thank you

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

Thank you!!

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

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 !

Thank you!

thanks