How to reset the primary key sequence in Postgres

Published on

Last Updated on

Estimated Reading Time: 1 min

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

Further Reading