Reset postgres sql auto increment key

Sometimes after you have imported data in to postgres sql database you might find the new inserts are failing as the generated unique sequence keys are conflicting with existing records.

You could fix this by setting the new sequence value to the next values by checking the highest number already exists in records. Please run the below query to fix the issue. The text in bold needs to be replaced by the actual table name and column name.

    (SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'column_name')),
    (SELECT (MAX("column_name") + 1) FROM "table_name"),

Use the below query to check the latest value set to the sequence after executing the above script.

SELECT nextval(PG_GET_SERIAL_SEQUENCE('"table_name"', 'column_name'));

Sometime resetting won’t help if the sequence itself is missing in the database. You can check all the sequence in the postgres database using below query.

SELECT sequence_schema, sequence_name 
FROM information_schema.sequences 
ORDER BY sequence_name

In that case you can create a sequence first before resetting the value to the sequence. The below query can be used to create a sequence.

CREATE SEQUENCE sequence_name
OWNED BY table_name.column_name;


Full stack developer, and Pega Certified Lead System architect since 2013 and with nearly 16 years experience in Pega. Connect with me in Linked in at

Leave a Reply

Your email address will not be published. Required fields are marked *