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.


SELECT SETVAL(
    (SELECT PG_GET_SERIAL_SEQUENCE('"table_name"', 'column_name')),
    (SELECT (MAX("column_name") + 1) FROM "item"),
    FALSE);

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

Leave a Comment