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

Question

I'm using PostgreSQL 9.3.

I want to duplicate some of the db records. Since I'm using an auto-increment pk id for the table, I want to get back the id mappings from the generated ids of duplicated records to the original ones. For example, say I have a table posts with 2 records in it:

 [{'id': 1, 'title': 'first'}
, {'id': 2. 'title': 'second'}]

With SQL:

INSERT INTO posts (title) SELECT title FROM posts RETURNING id, ??

I expect to see mappings like:

 [{'id': 3, 'from_id': 1}
, {'id': 4, 'from_id': 2}]

Any idea on how to fill in the question marks above to make it work? Thanks a lot!

Lahomalahore

I thought you might be able to do it by aliasing: FROM posts oldposts, then referencing the old and new aliases in the RETURNING clause. It doesn't look like it though. Tricky. – Generalship

@CraigRinger yeah, I tried that too. Wondering why it doesn't work that way. – Lahomalahore

it's a near-duplicate of stackoverflow.com/questions/7115941 which has no accepted answer. – Pyrotechnic

Answer 1

This would be simpler for UPDATE, where additional rows joined into the update are visible to the RETURNING clause:

• Return pre-UPDATE column values using SQL only

The same is currently not possible for INSERT. The manual:

The expression can use any column names of the table named by table_name

table_name being the target of the INSERT command.

You can use (data-modifying) CTEs to get this to work.
Assuming title to be unique per query, else you need to do more:

WITH sel AS (
   SELECT id, title
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   )
, ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel
   RETURNING id, title
 )
SELECT ins.id, sel.id AS from_id
FROM   ins
JOIN   sel USING (title);

If title is not unique per query (but at least id is unique per table):

WITH sel AS (
   SELECT id, title, row_number() OVER (ORDER BY id) AS rn
   FROM   posts
   WHERE  id IN (1,2)   -- select rows to copy
   ORDER  BY id
   )
, ins AS (
   INSERT INTO posts (title)
   SELECT title FROM sel ORDER  BY id  -- ORDER redundant to be sure
   RETURNING id
 )
SELECT i.id, s.id AS from_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i
JOIN   sel s USING (rn);

This second query relies on the undocumented implementation detail that rows are inserted in the order provided. It works in all current versions of Postgres and is probably not going to break.

db<>fiddle here
_{Old sqlfiddle}

Cleavable

great answer. the second last row in the second query should have the column alias outside of the partition clause FROM (SELECT id, row_number() OVER (ORDER BY id) AS rn FROM ins) i – Jake

Re "query relies on the undocumented implementation detail that rows are inserted in the order provided", @AdrianKlaver pointed me to postgresql.org/message-id/1678721.1681566044%40sss.pgh.pa.us where Tom Lane wrote "We […] rejected the idea that INSERT must preserve the order of the incoming tuples." – Orthopedist

Answer 2

if id column of posts is serial type, it's generated like nextval('posts_id_seq'::regclass), you can manually call this function for every new row

with
sel as (
  SELECT id, title, nextval('posts_id_seq'::regclass) new_id
  FROM   posts
  WHERE  id IN (1,2)
),
ins as (
  INSERT INTO posts (id, title)
  SELECT new_id, title
  FROM sel
)
SELECT id, new_id
FROM sel

it'l works with any data, include non-unique title

Queue

@ErwinBrandstetter What is your opinion on this method? – Varitype

Answer 3

The simplest solution IMHO would be to simply add a column to your table where you could put id of the row that was cloned.

Donegan

Yep, this may be the simplest and may also fit in other SQL dbs. But it adds additional complexity to db schema. – Lahomalahore