Обсуждение: 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@yahoo.com> wrote:

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.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

On Sep 7, 2012, at 2:19 PM, David Johnston wrote:

>
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.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
theoriginal 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
originalones 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')
returningrid, 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
onesfrom the copies. I could write a pl/pgsql function to loop through the records and build the mapping as I go, but I
wasthinking 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
getthe same effect but at a time when you have not forgotten what the old value was. 

I gave that a try; however, it seems that columns from the SELECT statement are not available for use in the RETURNING
clause.


>
> If for some reason you have to let the ID be auto-generated you likely need to identify the “natural key” for the
recordand 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.

I tried something along those lines using row_number().  I think perhaps it would, as you suggested, be better to poll
thesequence. 

WITH x as (
    SELECT row_number() over (order by rid asc) as rownum, rid, category, name, fk_parent FROM testing WHERE
category='cat1'
    ),
    y as (
        INSERT INTO test (name, fk_parent) select 'cat1-copy', name, fk_parent FROM x returning rid
    ),
    z as (
        SELECT row_number() over (order by rid asc) as rownum, rid FROM y
    )
SELECT x.rownum, z.rid as new_rid, x.rid as org_rid FROM z, x WHERE z.rownum=x.rownum;


Ultimately, I think doing a loop using pl/pgsql isn't so bad considering the number of records generally copied is
small.