Should I reset the sequence to MAX(id) or MAX(id)+1 after a data import?

February 5, 2023

As stated in the title, should I reset a PG sequence to MAX(id) or to MAX(id)+1 after a data import?

-- is what SELECT MAX(id) FROM my_table or
-- SELECT MAX(id)+1 FROM my_table 

ALTER SEQUENCE my_table_id_seq RESTART WITH what;

If I pick a table and do SELECT MAX(id) FROM my_table, I get x, then I look into the sequence last_value and it shows x=MAX(id) and NOT MAX(id)+1

So to me it locally means that PG keeps track (and should be reset to) the last value i.e. MAX(id) and not to MAX(id)+1

>Solution :

The docs at https://www.postgresql.org/docs/current/sql-altersequence.html say

The […] clause RESTART WITH restart changes the current value of the sequence. This is similar to calling the setval function with is_called = false: the specified value will be returned by the next call of nextval.

So you’ll need COALESCE((SELECT MAX(id) FROM my_table), 0) + 1, or the next nextval() call will return a value that already exists in your table.