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?
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.
