Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: The Hermit Hacker <scrappy(at)hub(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: using a join in an 'INSERT ... SELECT' ...
Date: 2000-09-14 05:17:03
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

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

In response to

pgsql-hackers by date

Next:From: Zeugswetter Andreas SBDate: 2000-09-14 07:47:07
Subject: AW: Status of new relation file naming
Previous:From: Franck MartinDate: 2000-09-14 05:14:45
Subject: Indexing for geographical objects

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group