Reset postgres sql auto increment key

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;

A full stack developer learning a developing web applications since my university time for more than 20 years now and Pega Certified Lead System Architect (since 2013) with nearly 16 years experience in Pega.

Leave a Reply

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

Back To Top