title: Persistent "unique constraint violation" on an empty table while inserting
body: <p>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.</p>
  
  <p>Here's my setup:</p>
  
  <pre><code>-- 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);
  </code></pre>
  
  <p>I have to create a function which makes a copy of the existing row in the <code>test</code> table. However, the following statement, which was supposed to do the job, fails:</p>
  
  <pre><code>INSERT INTO test_copy(copy_position, id, a, b, c)
      (SELECT 666, 3, t.a, t.b, t.c
          FROM test AS t);
  </code></pre>
  
  <p>The following error is issued:</p>
  
  <pre><code>ERROR:  duplicate key value violates unique constraint "test_copy_pkey"
  DETAIL:  Key (id)=(3) already exists.
  </code></pre>
  
  <p>The <code>test_copy</code> table is completely empty. The former statement is the only <code>INSERT</code> statement supplying the table with any rows and yet it somehow violates the unique constraint. Inserting the values manually, without the <code>SELECT</code> 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.</p>