When we insert a new row in a table with a sequential primary key, we get a "23505: duplicate key value violates unique constraint" error.
This implies that a record already exists for the next number being returned for the sequence.
The reason this happens is that the primary key sequence is out of sync with the table rows.
- The table name is "table"
- The column with the issue is "id."
Validate that the sequence is out-of-sync
Before we reset the sequence, let us make sure that it is out of sync.
SELECT nextval(PG_GET_SERIAL_SEQUENCE('"table"', 'id')); SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('"table"', 'id')) AS "Current Value", MAX("id") AS "Max Value" FROM "table";
Line #1: We make a call to nextval because if we call currval before calling nextval in the current session, it will give us an error similar to "ERROR: currval of sequence "table_id_seq" is not yet defined in this session".
Line #3-6: Gets the value most recently obtained by nextval for this sequence in the current session along with the maximum value for id in table.
Our sequence is out-of-sync when the Current Value is less than Max Value.
What does PG_GET_SERIAL_SEQUENCE do?
PG_GET_SERIAL_SEQUENCE('"table"', 'id') can be used to get the sequence name.
Using this, we can avoid hard-coding the actual sequence name, which helps prevent incorrect assumptions about the sequence name.
Note that the table name is in double-quotes, surrounded by single quotes.
SELECT SETVAL( (SELECT PG_GET_SERIAL_SEQUENCE('"table"', 'id')), (SELECT (MAX("id") + 1) FROM "table"), FALSE);
This will set the sequence to the next available value higher than any existing value for "id" in table. Further inserts will not result in a "duplicate key value violates unique constraint" error.