serial in postgres is being increased even though I added on conflict do nothing

by

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:

  1. Check values to insert against constraint
  2. If duplicate detected, abort
  3. Increment sequence
  4. 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:

  1. Resolve default values, including incrementing the sequence
  2. Check values to insert against constraint
  3. If duplicate detected, abort
  4. 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.

Leave a Comment

tech