0

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

SkyWalker
  • 13,729
  • 18
  • 91
  • 187
  • would the id not increment automatically with every insert? – nbk Feb 05 '23 at 15:25
  • it has the concept of current and next value so not sure and thus the OP – SkyWalker Feb 05 '23 at 15:26
  • https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync has the relevant discussion – Bergi Feb 05 '23 at 15:37
  • @Bergi, thanks but there is a discussion and not a spot answer to my question. The different answers dont bother too much about the detail between MAX or MAX+1. I believe should be MAX and not MAX+1, using MAX+1 would leave holes. – SkyWalker Feb 05 '23 at 15:40
  • 1
    @SkyWalker The "discussion" hinges on calling `set_val` with `true` or `false`. But most importantly, heed [Craig Ringer's advice](https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync#comment79586215_244265): "*If your application cares about gaps in sequences, your application is broken. Gaps in sequences are normal, and can occur due to unplanned database shutdowns, transaction rollbacks after errors, etc.*" – Bergi Feb 05 '23 at 15:44

1 Answers1

2

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.

Bergi
  • 630,263
  • 148
  • 957
  • 1,375