How to reset the primary key sequence in Postgres

Published on

Last Updated on

Estimated Reading Time: 1 min

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.


  • 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'));

    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.


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 PG_GET_SERIAL_SEQUENCE('"table"', 'id')),
    (SELECT (MAX("id") + 1) FROM "table"),

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