> 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 <robjsarg...@gmail.com> wrote: > > > On Aug 10, 2019, at 8:47 PM, Souvik Bhattacherjee <kivu...@gmail.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.kla...@aklaver.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.kla...@aklaver.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) > > >
