Re: RE: [GENERAL] INSERT. RETURNING for copying records

Hi David,

I am not sure the RETURNING offers you the following behavior ..

< What I'm looking for >****

+--------------+-----+****

| original_rid | rid |****

+--------------+-----+****

| 1 | 4 |****

| 2 | 5 |****

| 3 | 6 |****

+--------------+-----+****

**

I believe, the following example gives you the desired results once we
insert completes..

postgres=# SELECT * FROM TEST;
t | t1
---+--------
1 | Dinesh
2 | Dinesh
3 | Kumar
4 | Kumar
5 | Manoja
(5 rows)

postgres=# SELECT MIN(T),MAX(T),T1 FROM TEST GROUP BY T1 HAVING
MIN(T)!=MAX(T);
min | max | t1
-----+-----+--------
1 | 2 | Dinesh
3 | 4 | Kumar
(2 rows)

Best Regards,
Dinesh
manojadinesh.blogspot.com

On Sat, Sep 8, 2012 at 12:49 AM, David Johnston <polobo(at)yahoo(dot)com> wrote:

> ** **
>
> *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****
>