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 <kuldeeparor...@gmail.com> 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.v...@gmail.com> wrote: > >> >> >> On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh <kuldeeparor...@gmail.com> >> 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, >>> KD >>> >> >> insert 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 this >> insert 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 >> >>
