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

Question

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 1

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.

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.

Answer 2

You need to create a new id for every record.

Instead of

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

that use always the id 3 for each record present in test.

Try with the following code if id is of type autoincrement.

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

In this case you loose the original id. If you need to mantain the original id you need to change your table structure with something like the following:

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

and the insert becomes:

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