From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Copying records from TABLE_A to TABLE_B (in the same database) |
Date: | 2022-08-02 18:54:45 |
Message-ID: | 195a23f4-f640-3af2-207a-426888eefdb0@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 8/2/22 13:41, Adrian Klaver wrote:
> On 8/2/22 11:37 AM, Ron wrote:
>> AWS RDS Postgresql 12.10
>>
>> There are no indices or constraints (except for NOT NULL) on table_a.
>>
>> The two ways that I know are:
>> INSERT INTO table_a SELECT * FROM table_b;
Argh, I got the tables backwards. Should be:
INSERT INTO table_b SELECT * FROM table_a;
>> and
>> \COPY table_a TO '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>> \COPY table_b FROM '/tmp/table_a.tsv' WITH (FORMAT BINARY);
>>
>> Is there a faster/better way?
>>
> Does table_a have existing records?
Yes. Just before the copy, table_b was created using:
CREATE TABLE table_b (LIKE table_a INCLUDING CONSTRAINTS INCLUDING
DEFAULTS);
The only constraints on table_a are NOT NULL on various fields.
> If so do you care if there are duplicates?
TABLE_A (the source) has a UNIQUE index. I'll be adding a similar PK on
TABLE_B after the copy.
> How large a data set are you talking about?
It's varied. The biggest have up to 20M rows with a bytea field, and others
with 50M rather large (but no bytea) fields.
INSERT INTO is good enough for the small tables.
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2022-08-02 18:59:36 | Re: Copying records from TABLE_A to TABLE_B (in the same database) |
Previous Message | Thomas Kellerer | 2022-08-02 18:51:46 | Re: Copying records from TABLE_A to TABLE_B (in the same database) |