RE: [GENERAL] INSERT. RETURNING for copying records

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Michael Sacket
Sent: Friday, September 07, 2012 2:09 PM
To: PG-General Mailing List
Subject: [GENERAL] INSERT. RETURNING for copying records

Good Afternoon,

I'm attempting to write a function that will duplicate a few records, but
the catch is I need to have a mapping of the original pk to the new pk. I
know I can use the RETURNING clause to get the new ids... but how to map
that to the original ones is escaping me.

< Setup >

CREATE TABLE testing (rid serial PRIMARY KEY, category text NOT NULL, name
text NOT NULL, fk_parent int4);

INSERT INTO testing (category, name, fk_parent) VALUES ('cat1', 'one',
NULL), ('cat1', 'one.one', 1), ('cat1', 'one.two', 1);

SELECT * FROM testing;
+-----+----------+---------+-----------+
| rid | category | name | fk_parent |
+-----+----------+---------+-----------+
| 1 | cat1 | one | NULL |
| 2 | cat1 | one.one | 1 |
| 3 | cat1 | one.two | 1 |
+-----+----------+---------+-----------+

< Duplicating the records >

INSERT INTO testing (category, name, fk_parent) (select category, name,
fk_parent from testing where category='cat1') returning rid, category, name,
fk_parent;

+-----+----------+---------+-----------+

| rid | category | name | fk_parent |

+-----+----------+---------+-----------+

| 4 | cat1 | one | NULL |

| 5 | cat1 | one.one | 1 |

| 6 | cat1 | one.two | 1 |

+-----+----------+---------+-----------+

< What I'm looking for >

+--------------+-----+

| original_rid | rid |

+--------------+-----+

| 1 | 4 |

| 2 | 5 |

| 3 | 6 |

+--------------+-----+

< This doesn't work >

INSERT INTO testing (category, name, fk_parent) select category, name,
fk_parent from testing as original where category='cat1' returning rid,
category, name, fk_parent, original.rid;

Specifically, my goal is to be able to duplicate a subset of records and map
any referenced foreign keys to the new ones from the copies. I could write a
pl/pgsql function to loop through the records and build the mapping as I go,
but I was thinking there might be a better way. Any thoughts?

Thanks!

Michael

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Two thoughts (syntax not validated):

INSERT INTO .. VALUES (non-id-cols, id)

SELECT [non-id-cols], nextval('sequence') AS new_id FROM testing

RETURNING id, new_id

There is no reason to delay the assignment of the ID until the time of
insert; by polling the sequence manually you get the same effect but at a
time when you have not forgotten what the old value was.

If for some reason you have to let the ID be auto-generated you likely need
to identify the "natural key" for the record and then:

WITH ins (

INSERT .. RETURNING newid, (natural_key_cols) AS natrualkey

)

SELECT *

FROM ins

JOIN testing ON

ins.naturalkey = (testing.natural_key cols)

If there is no natural key then this method is ambiguous in the presence of
multiple otherwise identical records.

David J