From: | Arun Suresh <arun(dot)suresh(dot)303(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to copy rows into same table efficiently |
Date: | 2021-10-26 06:04:40 |
Message-ID: | CA+7NA6aNAAKByWQ9yNN9kq7nDDk3+eyT95Ur9g60nRshmu8-xQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Dear PG experts,
We have a tenant discriminator column in our tables to enable storage of
data from multiple
tenants. This column is also part of the composite primary key.
The customers may request creation of a tenant copy, which means if they
currently have a
tenant id "ABCD", they would like to copy all data "where tenant_id =
'ABCD'" to a new copy tenant "XYZ".
The copy must also be done on the same table.
Current approach taken is to build a query like below:
INSERT INTO mytable (col1, col2, col3, col4) SELECT col1, 'XYZ', col3, col4
FROM mytable WHERE col2 = 'ABCD'
Is there a better way to do this?
There could be other tables with foreign key reference, would a simple
ordering of the copy based on table relationship suffice?
Also if the table has millions of records, what are the things to take care
of?
Regards,
Arun Suresh
From | Date | Subject | |
---|---|---|---|
Next Message | Vivekk P | 2021-10-26 06:09:01 | Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND |
Previous Message | Yi Sun | 2021-10-26 02:33:15 | plpython3 package installation problem |