Re: How to copy rows into same table efficiently

On Tue, Oct 26, 2021 at 1:07 AM Arun Suresh <arun(dot)suresh(dot)303(at)gmail(dot)com>
wrote:

> 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?
>

The other concern I would have is having a rolling view of the data in
default read committed mode. If you copy data from a primary table (with
other tables having fkey to that table coming later in the process), then
you may need to use some created_on < script_start_time, else when you try
to insert dependent rows where the record in the primary table did not
exist yet when you started your process and your rows in dependent table
want to reference that primary table row... error.

Or, use REPEATABLE READ, but WAL buildup may be a concern.