serial in postgres is being increased even though I added on conflict do nothing
The reason this feels weird to you is that you are thinking of the increment on the counter as part of the insert operation, and therefore the "DO NOTHING" ought to mean "don't increment anything". You're picturing this:
- Check values to insert against constraint
- If duplicate detected, abort
- Increment sequence
- Insert data
But in fact, the increment has to happen before the insert is attempted. A SERIAL
column in Postgres is implemented as a DEFAULT
which executes the nextval()
function on a bound SEQUENCE
. Before the DBMS can do anything with the data, it's got to have a complete set of columns, so the order of operations is like this:
- Resolve default values, including incrementing the sequence
- Check values to insert against constraint
- If duplicate detected, abort
- Insert data
This can be seen intuitively if the duplicate key is in the autoincrement field itself:
CREATE TABLE foo ( id SERIAL NOT NULL PRIMARY KEY, bar text );
-- Insert row 1
INSERT INTO foo ( bar ) VALUES ( 'test' );
-- Reset the sequence
SELECT setval(pg_get_serial_sequence('foo', 'id'), 0, true);
-- Attempt to insert row 1 again
INSERT INTO foo ( bar ) VALUES ( 'test 2' )
ON CONFLICT (id) DO NOTHING;
Clearly, this can't know if there's a conflict without incrementing the sequence, so the "do nothing" has to come after that increment.
As already said by @a_horse_with_no_name and @Serge Ballesta serials are always incremented even if INSERT
fails.
You can try to "rollback" serial value to maximum id
used by changing the corresponding sequence:
SELECT setval('sometable_id_seq', MAX(id), true) FROM sometable;
As said by @a_horse_with_no_name, that is by design. Serial type fields are implemented under the hood through sequences, and for evident reasons, once you have gotten a new value from a sequence, you cannot rollback the last value. Imagine the following scenario:
- sequence is at n
- A requires a new value : got n+1
- in a concurrent transaction B requires a new value: got n+2
- for any reason A rollbacks its transaction - would you feel safe to reset sequence?
That is the reason why sequences (and serial field) just document that in case of rollbacked transactions holes can occur in the returned values. Only unicity is guaranteed.