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

On 8/2/22 12: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

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


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)

If you can get outside sql, the bulk copy facilities (CopyManager in java) is blindingly fast for me.