Thread: using a join in an 'INSERT ... SELECT' ...

Okay, logically I think this makes sense, but its not working ... should
it?

globalmatch=# insert into auth_info_new
globalmatch-# select ai.* from auth_info ai, auth_info_new ain
globalmatch-# where ai.username != ain.username;
INSERT 0 0

auth_info has 14k tuples, but some are duplicates ... I want to insert
into auth_info_new everything except those that have already been inserted
into auth_info_new ...

now, my first thought looking at the above would be that since
auth_info_new is empty, all from auth_info should be copied over ...
basically, since each tuple isn't "committed" until the insert is
finished, then every username in ai is definitely not in ain ... but
nothing is being copied over, so my first thought is definitely wrong ...

bug? *raised eyebrow*  

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 

On Thu, 14 Sep 2000, The Hermit Hacker wrote:

> 
> Okay, logically I think this makes sense, but its not working ... should
> it?
> 
> globalmatch=# insert into auth_info_new
> globalmatch-# select ai.* from auth_info ai, auth_info_new ain
> globalmatch-# where ai.username != ain.username;
> INSERT 0 0
> 
> auth_info has 14k tuples, but some are duplicates ... I want to insert
> into auth_info_new everything except those that have already been inserted
> into auth_info_new ...
> 
> now, my first thought looking at the above would be that since
> auth_info_new is empty, all from auth_info should be copied over ...
> basically, since each tuple isn't "committed" until the insert is
> finished, then every username in ai is definitely not in ain ... but
> nothing is being copied over, so my first thought is definitely wrong ...
>
> bug? *raised eyebrow*  

Nah. Remember, you're doing a product with that join.  If there are no
rows in auth_info_new, there are no rows after the join to apply the where
to.

You really want something like (untested):
insert into auth_info_newselect ai.* from auth_info ai where not exists ( select * from auth_info_new ain where
ai.username=ain.username);