From: | Michael Sacket <msacket(at)gammastream(dot)com> |
---|---|
To: | PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | INSERT… RETURNING for copying records |
Date: | 2012-09-07 18:09:05 |
Message-ID: | 08CB5792-2AD7-49C9-8FB2-5E45D7CEA456@gammastream.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2012-09-07 18:44:22 | Re: Multiple indexes, huge table |
Previous Message | Merlin Moncure | 2012-09-07 15:41:26 | Re: Moving several databases into one database with several schemas |