How to reset the primary key sequence when it falls out of sync in Postgres?

This entry was posted on

  • postgres
  • sql
  • snippet

The Problem

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 because the primary key sequence is out of sync with the table rows.

Assumptions

  • 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.

When the Current Value is less than Max Value, our sequence is out-of-sync.

What does PG_GET_SERIAL_SEQUENCE do?

PG_GET_SERIAL_SEQUENCE('"table"', 'id') can be used to get the sequence name.

By using this we can avoid hard-coding the actual sequence name which helps to avoid any incorrect assumptions about the sequence name.

Note that the table name is in double-quotes, surrounded by single quotes.

The Fix

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 that is higher than any existing value for ”id” in table. Further inserts will not result in a “duplicate key value violates unique constraint” error.

References

More like this

Ankur Sheel © 2021
Connect with me
GithubTwitterLinkedIn