Efficiently duplicate some rows in PostgreSQL table

Question

I have PostgreSQL 9 database that uses auto-incrementing integers as primary keys. I want to duplicate some of the rows in a table (based on some filter criteria), while changing one or two values, i.e. copy all column values, except for the ID (which is auto-generated) and possibly another column.

However, I also want to get the mapping from old to new IDs. Is there a better way to do it then just querying for the rows to copy first and then inserting new rows one at a time?

Essentially I want to do something like this:

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

However, this fails with ERROR: missing FROM-clause entry for table "old" and I can see why: Postgres must be doing the SELECT first and then inserting it and the RETURNING clauses only has access to the newly inserted row.

Answer 1

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.