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

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

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

References

• Sequence Manipulation Functions