Tomalla Tomalla - 3 years ago 134
SQL Question

Persistent "unique constraint violation" on an empty table while inserting

Let's assume I'm managing a simple table. Additionally, each user can indirectly create a copy of each row and modify it on their own.

Here's my setup:

-- the original table
CREATE TABLE test
(
id integer PRIMARY KEY,
a integer,
b integer NOT NULL,
c integer
);

-- the table holding a modified copy of the former table
CREATE TABLE test_copy
(
copy_position integer NOT NULL, -- some additional data attached to the copy
id integer PRIMARY KEY REFERENCES test(id), -- the id of the copied row
a integer,
b integer NOT NULL,
c integer
);

-- some sample data
INSERT INTO test VALUES (1, 4, 4, 4), (2, 7, 3, 2), (3, 72, 23, 7), (4, 11, 22, 33);


I have to create a function which makes a copy of the existing row in the
test
table. However, the following statement, which was supposed to do the job, fails:

INSERT INTO test_copy(copy_position, id, a, b, c)
(SELECT 666, 3, t.a, t.b, t.c
FROM test AS t);


The following error is issued:

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


The
test_copy
table is completely empty. The former statement is the only
INSERT
statement supplying the table with any rows and yet it somehow violates the unique constraint. Inserting the values manually, without the
SELECT
subquery is executed successfully. After few hours of researching I've run out of ideas what might be the reason for the error and I feel like the solution to this problem must be really simple. I'm using PostgreSQL 9.4.

Answer Source

Well, the question was a complete non-event. It has been answered withing the first two minutes by @a_horse_with_no_name in the comments section after it was posted (thank you for it) and the problem itself was nearly a rookie mistake.

Now, after waiting a few days, I feel committed to close the question by providing a legitimate answer, however obvious it would be.

I completely forgot about the WHERE clause in my SELECT subquery. It should be written as follows instead:

INSERT INTO test_copy(copy_position, id, a, b, c)
    (SELECT 666, t.id, t.a, t.b, t.c
        FROM test AS t WHERE t.id = 3);

And that would be it for this question.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download