Re: Bulk Inserts

> Does this appeal to you:
> COPY (SELECT * FROM relation) TO ... (
https://www.postgresql.org/docs/10/sql-copy.html)

Not sure if COPY can be used to transfer data between tables.

On Sat, Aug 10, 2019 at 11:01 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee <kivuosb(at)gmail(dot)com>
> wrote:
>
> Hi Adrian,
>
> Thanks for the response.
>
> > Yes, but you will some code via client or function that batches the
> > inserts for you.
>
> Could you please elaborate a bit on how EXP 1 could be performed such that
> it uses bulk inserts?
>
> Best,
> -SB
>
> On Fri, Aug 9, 2019 at 7:26 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 8/9/19 3:06 PM, Souvik Bhattacherjee wrote:
>> > Hi,
>> >
>> > I'm trying to measure the performance of the following: Multiple txns
>> > inserting tuples into a table concurrently vs single txn doing the
>> whole
>> > insertion.
>> >
>> > *new table created as:*
>> > create table tab2 (
>> > id serial,
>> > attr1 integer not null,
>> > attr2 integer not null,
>> > primary key(id)
>> > );
>> >
>> > *EXP 1: inserts with multiple txn:*
>> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
>> > attr2 = 10);
>> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1 where
>> > attr2 = 20);
>> >
>> > note: attr2 has only two values 10 and 20
>> >
>> > *EXP 2: inserts with a single txn:*
>> > insert into tab2 (attr1, attr2) (select attr1, attr2 from tab1);
>> >
>> > I also performed another experiment as follows:
>> > *EXP 3:* select attr1, attr2 into tab2 from tab1;
>> >
>> > The observation here is EXP 3 is much faster than EXP 2 probably due
>> to
>> > bulk inserts used by Postgres. However I could not find a way to insert
>> > id values in tab2 using EXP 3. Also select .. into .. from .. throws an
>> > error if we create a table first and then populate the tuples using the
>> > command.
>>
>> Yes as SELECT INTO is functionally the same as CREATE TABLE AS:
>>
>> https://www.postgresql.org/docs/11/sql-selectinto.html
>>
>> >
>> > I have the following questions:
>> > 1. Is it possible to have an id column in tab2 and perform a bulk
>> insert
>> > using select .. into .. from .. or using some other means?
>>
>> Not using SELECT INTO for reasons given above.
>> Though it is possible to SELECT INTO as you show in EXP 3 and then:
>> alter table tab2 add column id serial primary key;
>> EXP 2 shows the other means.
>>
>> > 2. If a table is already created, is it possible to do bulk inserts via
>> > multiple txns inserting into the same table (EXP 3)?
>>
>> Yes, but you will some code via client or function that batches the
>> inserts for you.
>>
>> >
>> > Best,
>> > -SB
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com
>>
> Top-posting (i.e. putting your reply at the top is discouraged here)
> Does this appeal to you:
>
> COPY (SELECT * FROM relation) TO ... (
> https://www.postgresql.org/docs/10/sql-copy.html)
>
>
>