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.

More Related Contents:

  1. Insert, on duplicate update in PostgreSQL?
  2. How to UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
  3. Postgres: INSERT if does not exist already
  4. How to convert primary key from integer to serial?
  5. What’s the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?
  6. Use multiple conflict_target in ON CONFLICT clause
  7. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  8. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  9. Changing primary key int type to serial
  10. Differentiate inserted and updated rows in UPSERT using system columns
  11. postgres autoincrement not updated on explicit id inserts
  12. PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values
  13. Why the “Group BY” by month didn’t work? [closed]
  14. What to do with null values when modeling and normalizing?
  15. postgres default timezone
  16. quick random row selection in Postgres
  17. How to close idle connections in PostgreSQL automatically?
  18. psql: FATAL: database “” does not exist
  19. Postgres FOR LOOP
  20. Storing Images in PostgreSQL
  21. Strange behaviour in Postgresql
  22. Postgres: Distinct but only for one column
  23. In PostgreSQL, how to insert data with COPY command?
  24. Cursor based records in PostgreSQL
  25. Configuration parameter work_mem in PostgreSQL on Linux
  26. How to work with PGpoint for Geolocation using PostgreSQL?
  27. How to delete an enum type value in postgres?
  28. Why unsigned integer is not available in PostgreSQL?
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment