Efficiently duplicate some rows in PostgreSQL table

RETURNING can only refer to the columns in the final, inserted row. You cannot refer to the "OLD" id this way unless there is a column in the table to hold both it and the new id.

Try running this which should work and will show all the possible values that you can get via RETURNING:

INSERT INTO my_table (col1, col2, col3)    SELECT col1, 'new col2 value', col3    FROM my_table AS old    WHERE old.some_criteria = 'something'RETURNING *;

It won't get you the behavior you want, but should illustrate better how RETURNING is designed to work.

This can be done with the help of data-modifiying CTEs (Postgres 9.1+):

WITH sel AS (   SELECT id, col1, col3        , row_number() OVER (ORDER BY id) AS rn  -- order any way you like   FROM   my_table   WHERE  some_criteria = 'something'   ORDER  BY id  -- match order or row_number()   ),    ins AS (   INSERT INTO my_table (col1, col2, col3)   SELECT col1, 'new col2 value', col3   FROM   sel   ORDER  BY id  -- redundant to be sure   RETURNING id )SELECT s.id AS old_id, i.id AS new_idFROM  (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) iJOIN   sel s USING (rn);

SQL Fiddle demonstration.

This relies on the undocumented implementation detail that rows from a SELECT are inserted in the order provided (and returned in the order provided). It works in all current versions of Postgres and is not going to break. Related:

• Does Postgres preserve insertion order of records?

Window functions are not allowed in the RETURNING clause, so I apply row_number() in another subquery.

More explanation in this related later answer:

• INSERT INTO ... FROM SELECT ... RETURNING id mappings

Good! I test this code, but I changethis (FROM my_table AS old) in (FROM my_table) andthis (WHERE old.some_criteria = 'something') in (WHERE some_criteria = 'something')

This is the final code that I use

INSERT INTO my_table (col1, col2, col3)    SELECT col1, 'new col2 value', col3    FROM my_table AS old    WHERE some_criteria = 'something'RETURNING *;

Thanks!