On Tue, Oct 26, 2010 at 5:54 PM, Mladen Gogala <mladen.gogala@xxxxxxxxxxx> wrote: > On 10/26/2010 5:27 PM, Jon Nelson wrote: >> >> start loop: >> populate rows in temporary table >> insert from temporary table into permanent table >> truncate temporary table >> loop >> >> I do something similar, where I COPY data to a temporary table, do >> lots of manipulations, and then perform a series of INSERTS from the >> temporary table into a permanent table. >> > > 1) It's definitely not faster because you have to insert into the temporary > table, in addition to inserting into the permanent table. > 2) This is what I had in mind: > > mgogala=# create table a(c1 int); > CREATE TABLE > mgogala=# create temporary table t1(c1 int) on commit delete rows; > CREATE TABLE > mgogala=# begin; > BEGIN > mgogala=# insert into t1 select generate_series(1,1000); > INSERT 0 1000 > mgogala=# insert into a select * from t1; > INSERT 0 1000 > mgogala=# commit; > COMMIT > mgogala=# select count(*) from a; > count > ------- > 1000 > (1 row) > > mgogala=# select count(*) from t1; > count > ------- > 0 > (1 row) > > The table is created with "on commit obliterate rows" option which means > that there is no need to do "truncate". The "truncate" command is a heavy > artillery. Truncating a temporary table is like shooting ducks in a duck > pond, with a howitzer. You are not paying attention ;-). Look upthread: "I am inserting each row in a separate transaction, due to design of my program." (also on commit/drop is no picnic either, but I digress...) merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
