postgresql | Reset the Identity value after an on conflict

In PostgreSQL, the ON CONFLICT clause is used in conjunction with the INSERT statement to handle conflicts that may arise when inserting data into a table with a unique constraint or a unique index. However, the ON CONFLICT clause itself doesn’t directly affect the identity (auto-increment) column.

If you’re looking to reset the identity column (also known as a serial or auto-increment column) after an ON CONFLICT action, you would need to handle this separately. PostgreSQL manages the value of the identity column, and adjusting the sequence that generates those values would be necessary to reset it to a specific value.

Here’s a general approach to reset the identity column after an ON CONFLICT action in postgresql:

1. Identify the sequence associated with the identity column: A sequence backs every identity column in PostgreSQL. You’ll need to find out the name of the sequence associated with your identity column.
2. Reset the sequence: You can reset the sequence by using the SETVAL function. You can set the next value of the sequence to the appropriate value based on the data you’re inserting.

Here’s an example SQL code snippet to demonstrate the process:– Assume “my_table” has an identity column called “id”

INSERT INTO my_table (id, column1, column2)
VALUES (42, 'data1', 'data2')
ON CONFLICT (id) DO NOTHING;

-- Get the name of the sequence associated with the identity column
SELECT pg_get_serial_sequence('my_table', 'id') INTO temp_sequence_name;

-- Reset the sequence to a value that's greater than the highest existing id
SELECT max(id) + 1 FROM my_table INTO temp_next_value;

-- Reset the sequence value
EXECUTE 'SELECT setval(''' || temp_sequence_name || ''', ' || temp_next_value || ', false)';

Please note that handling identity columns and sequences requires careful consideration, as resetting sequences can potentially lead to integrity issues if not done correctly. Always make sure to take proper backups and thoroughly test any changes you plan to make to your database schema.

Additionally, this example assumes that you’re manually specifying values for the identity column in the INSERT statement. If you’re relying on the auto-increment functionality, you might need to adjust the approach accordingly.

You can read this same article on my Medium blog.