copy syntax can include any valid select statement
COPY (any valid select statement joining tables and converting it row_to_json) TO 'some_dump_file'
or can copy a view
CREATE VIEW myview (any valid select statement joining tables and converting it row_to_json);
COPY myview TO 'some_dump_file'
Regards
Hector Vass
07773 352559
On Sat, Mar 9, 2024 at 4:01 PM kuldeep singh <kuldeeparora89@xxxxxxxxx> wrote:
Copy may not work in our scenario since we need to join data from multiple tables & then convert it to json using row_to_json . This json data eventually needs to be stored in a target table .Will it be better if we break the process into batches of like 10,000 rows & insert the data in its individual transactions? Or any other better solution available ?On Sat, Mar 9, 2024 at 9:01 PM hector vass <hector.vass@xxxxxxxxx> wrote:On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh <kuldeeparora89@xxxxxxxxx> wrote:Hi,We are inserting data close to 1M record & having a single Jsonb column but query is getting stuck.We are using insert into select * .. , so all the operations are within the DB.If we are running select query individually then it is returning the data in 40 sec for all rows but with insert it is getting stuck.PG Version - 15.What could be the problem here ?Regards,KDinsert 1M rows especially JSON that can be large, variable in size and stored as blobs and indexed is not perhaps the correct way to do thisinsert performance will also depend on your tuning. Supporting transactions, users or bulk processing are 3x sides of a compromise.you should perhaps consider that insert is for inserting a few rows into live tables ... you might be better using copy or \copy, pg_dump if you are just trying to replicate a large table
