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

[Rob Sargent <robjsargent@xxxxxxxxx>]

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.