PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: Clone rows but replace one column

Image I have the following table: (in reality there are more columns)

 user_id | product_id
---------|------------
        1|           1
        1|           2
        1|           3
        2|           2
        2|           3

Basically, I need to copy all rows with user_id = 1 but change it to user_id = 3:

 user_id | product_id
---------|------------
        1|           1
        1|           2
        1|           3
        2|           2
        2|           3
        3|           1
        3|           2
        3|           3
Tags:

2

Answers


  1. 0 
    create table clone_test( user_id integer, product_id integer);

insert into clone_test values (1,1), (1,2), (1,3), (2,2), (2,3);

insert into clone_test (select 3, product_id from clone_test where user_id =1);

select * from clone_test ;
 user_id | product_id 
---------+------------
       1 |          1
       1 |          2
       1 |          3
       2 |          2
       2 |          3
       3 |          1
       3 |          2
       3 |          3
    Login or Signup to reply.
  2. 0

    copy all rows with user_id = 1 but change it to user_id = 3:

    As already demonstrated by @Adrian Klaver, that’s a simple query with a constant in place of the user_id:
    demo at db<>fiddle

    insert into your_table 
select 3 as user_id, product_id 
from your_table
where user_id=1;

    Is there any way to do it without manually listing all other columns?

    There is, but

    • modern IDEs can typically be configured to identify your db schema, so they can add those columns on their own, without you having to type all of them out
    • dynamic SQL can be used to query your information_schema to add those columns into your query, but that’s pretty ugly
    • both dynamic SQL and the jsonb trick are quite costly and ugly, so it’s better to make sure benefits of this justify that price

    You can use auto-mapping Postgres does when converting records to and from jsonb:

    insert into clone_test 
select (jsonb_populate_record( null::clone_test
                              ,jsonb_set(to_jsonb(clone_test) 
                                         ,'{user_id}'
                                         ,to_jsonb(3))
                             )
       ).*
from clone_test
where user_id=1;
    1. to_jsonb() grabs entire rows from the table and maps them to jsonb.
    2. In each, jsonb_set() changes user_id to a 3.
    3. jsonb_populate_record maps the updated jsonb back to a clone_test record.
    4. ().* unpacks the record, separating the columns.

    Doing all that just to avoid having to tab through auto-completion, type out or copy the column list, is an overkill.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search