CODEWITHSUNDEEP
postgresqlinsert
ssc
ssc

Reputation: 9883

PostgreSQL: trivial INSERT fails the first time, succeeds afterwards

I am puzzled by a weird Postgres problem I encounter in the trivial database shown below: If I first insert a tag and explicitly specify its ID and then try to insert another tag without passing an ID, then this second insert fails. If I try a third time (again without ID), the insert succeeds.

DROP DATABASE IF EXISTS mydb;
CREATE DATABASE mydb;

\c mydb

DROP SCHEMA public;
CREATE SCHEMA core;

CREATE TABLE core.tag
(
    id serial PRIMARY KEY,
    title text NOT NULL
);

-- this works: all columns specified explicitly
INSERT INTO core.tag(id, title) VALUES (1, 'known tag');

-- omitting the tag ID fails with
-- ERROR:  duplicate key value violates unique constraint "tag_pkey"
-- DETAIL:  Key (id)=(1) already exists.
INSERT INTO core.tag(title) VALUES ('unknown tag');

-- this works again ?!?
INSERT INTO core.tag(title) VALUES ('unknown tag');

The issue only seems to occur on a freshly created database and once it does, it does not seem to happen again. I have never come across anything like this - so far, I have just inserted data with or without explicit ID and AFAICS, nothing ever failed like this...

Does anyone have an idea what's going on here ?!?

Environment: PostgreSQL 9.1.3 on Mac OSX 10.7.5

Upvotes: 3

Views: 510

Answers (1)

user330315
user330315

Reputation:

Of course this fails.

What happens?

When you create the table, a sequence is also created that generates the values for the ID column. The sequence starts with 1 but it is only used if you do not specify a value for the ID column.

Now when you run 

INSERT INTO core.tag(id, title) VALUES (1, 'known tag');

you bypass Postgres' automatic assigment of the ID value, the sequence "stays" at one.

Now when you run

INSERT INTO core.tag(title) VALUES ('unknown tag');

Postgres takes the next value from the sequence - which is 1. But that alreay exists so the insert fails. After taking the value from the sequence, the next value is 2, so the subsequent insert without specifying an ID value gets the 2 and succeeds.

The solution is to either never include the ID column in your inserts. Or - if you do - request the ID from the sequence:

INSERT INTO core.tag(id, title) VALUES (nextval('tag_id_seq'), 'known tag');

When a serial column is created it is automatically associated with a sequence which is named <table_name>_<column_name>_seq. And that's the name I used in the above statement.

More details about how the serial "data type" works are in the manual: http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

Upvotes: 7

Related Questions