postgres | Reset the Identity value after an on conflict

I expected postgres to handle this by itself, but

Photo by Claudio Schwarz on Unsplash

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. The identity column's value is managed by PostgreSQL, and resetting it to a specific value would involve adjusting the sequence that generates those values.

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

1. Identify the sequence associated with the identity column: Every identity column in PostgreSQL is backed by a sequence. 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: