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.