PostgreSQL BIGSERIAL and “duplicate key” on insert

Question

I have a table

CREATE TABLE users (
  id BIGSERIAL NOT NULL PRIMARY KEY,
  created_at TIMESTAMP DEFAULT NOW()
);

First I run

INSERT INTO users (id) VALUES (1);

After I run

INSERT INTO users (created_at) VALUES ('2016-11-10T09:37:59+00:00');

And I get

ERROR: duplicate key value violates unique constraint "users_pkey" DETAIL: Key (id)=(1) already exists.

Why id sequence is not incremented when I insert "id" by myself?

Laurenz Albe Laurenz Albe · Accepted Answer · 2016-11-14T10:37:20

That is because the DEFAULT clause only gets evaluated if you either omit the column in the SET clause or insert the special value DEFAULT.

In your first INSERT, the DEFAULT clause is not evaluated, so the sequence is not increased. Your second INSERT uses the DEFAULT clause, the sequence is increased and returns the value 1, which collides with the value explicitly given in the previous INSERT.

Don't mix INSERTs with automatic value creation using sequences and INSERTs that explicitly specify the column. Or if you have to, you sould make sure that the values cannot collide, e.g. by using even numbers for automatically generated values and odd numbers for explicit INSERTs.

PostgreSQL BIGSERIAL and “duplicate key” on insert

1 Answers