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
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
The docs at https://www.postgresql.org/docs/current/sql-altersequence.html say
The […] clause
RESTART WITH restartchanges the current value of the sequence. This is similar to calling the
is_called = false: the specified value will be returned by the next call of
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.