Re: Insert with Jsonb column hangs

Thanks Adrian & Hector .

I will try the copy approach & post the result here.

On Sat, Mar 9, 2024 at 9:57 PM hector vass <hector(dot)vass(at)gmail(dot)com> wrote:

>
>
> On Sat, Mar 9, 2024 at 4:10 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 3/9/24 08:00, kuldeep singh 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 .
>>
>> Per:
>>
>> https://www.postgresql.org/docs/current/sql-copy.html
>>
>> "
>> COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
>>
>> <...>
>>
>> query
>>
>> A SELECT, VALUES, INSERT, UPDATE, or DELETE command whose results
>> are to be copied. Note that parentheses are required around the query.
>>
>> For INSERT, UPDATE and DELETE queries a RETURNING clause must be
>> provided, and the target relation must not have a conditional rule, nor
>> an ALSO rule, nor an INSTEAD rule that expands to multiple statements.
>> "
>>
>> >
>> > 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(dot)vass(at)gmail(dot)com
>> > <mailto:hector(dot)vass(at)gmail(dot)com>> wrote:
>> >
>> >
>> >
>> > On Sat, Mar 9, 2024 at 3:02 PM kuldeep singh
>> > <kuldeeparora89(at)gmail(dot)com <mailto:kuldeeparora89(at)gmail(dot)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
>> >
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>
>
> What Adrian Klaver said ^
> discovered even this works...
>
>
> create view myview as (select row_to_json from mytable);
>
> create table newtable as select * from myview where 1=0;
>
> copy myview to program 'psql mydb postgres -c ''copy newtable from stdin''
> ';
>
>
>
>
>