And don't forget that \COPY and especially COPY are usually much faster (and, IMHO, easier to compose/maintain) than gobs of INSERTs.
> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Aaron Bono > Sent: Tuesday, October 10, 2006 1:46 PM > To: Dirk Jagdmann > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] optimal insert > > On 10/8/06, Dirk Jagdmann <[EMAIL PROTECTED]> wrote: > > Hello experts, > > I have a database that contains three tables: > > create table a ( > id serial primary key, > ... -- some more fields not relevant for my question > ); > create table b ( > id serial primary key, > ... -- some more fields not relevant for my question > ); > create table a_b ( > a int not null references a, > b int not null references b > ); > > Tables a and b have already been filled with lots of > rows. Now my > application needs to insert the relationship of a to b > into table a_b > and is currently doing it with inserts like the following: > > insert into a_b(a,b) values(1,100); > insert into a_b(a,b) values(1,200); > insert into a_b(a,b) values(1,54); > insert into a_b(a,b) values(1,4577); > > So for a batch of inserts the value of a stays the > same, while for by > arbitrary values are inserted. Now I have wondered if > PostreSQL offers > a smarter way to insert those values? A solution can > contains usage of > some plpgsql code. > > > It depends on your logic. If you can write a query that > selects out the a and b records, there is a smart way: > > insert into a_b(a, b) > select 1, b.id from b where b.id in (100, 200, 54, 4577); > > This is not really smart because you already have the id > values - the select may diminish your performance rather than > help. But if the select is "smarter" then you don't even > need to know what the b.id <http://b.id> values are: > > insert into a_b(a, b) > select 1, b.id from b where b.somecolumn = 'somevalue'; > > Hope this helps. > > > > ================================================================== > Aaron Bono > Aranya Software Technologies, Inc. > http://www.aranya.com > http://codeelixir.com > > ================================================================== > > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
