Re: Copying records from TABLE_A to TABLE_B (in the same database)

On 8/2/22 13:51, Thomas Kellerer wrote:
> Ron schrieb am 02.08.2022 um 20:37:
>> 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;
>> 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?
>
> The INSERT is most probably faster then \copy

That's what I figured, since COPY means moving data over the (admittedly
very fast) wire, onto disk, and then back over the wire into the new table.

>
> Another option is to have a trigger on table_a
> to automatically replay all DML on table_b

How would you originally populate it?

> Logical replication might be another option.
> Although I am not sure if that is even possible inside
> the samme database.
> I know it's tricky inside the same server
> (between different databases)

--
Angular momentum makes the world go 'round.