pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

not exists clause
3+ messages / 3 participants
[nested] [flat]

* not exists clause
@ 2008-01-10 20:15  S Golly <gollykai@gmail.com>
  0 siblings, 2 replies; 3+ messages in thread

From: S Golly @ 2008-01-10 20:15 UTC (permalink / raw)
  To: pgsql-performance

I cannot get the "not exists" clause of ANSI SQL to execute correctly.
select t.col11, t.col1... from table1 t where not exists (select 1 from
table2 where col2 = t.col1);
table1 has 40M + rows. if that matters.

OS is FreeBSD 6.2, postgresql version 8.2.6

Is it not supported or a bug ?
thank you for your support.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: not exists clause
@ 2008-01-10 21:50  Josh Berkus <josh@agliodbs.com>
  parent: S Golly <gollykai@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Josh Berkus @ 2008-01-10 21:50 UTC (permalink / raw)
  To: pgsql-performance; +Cc: S Golly <gollykai@gmail.com>

Golly,

> I cannot get the "not exists" clause of ANSI SQL to execute correctly.
> select t.col11, t.col1... from table1 t where not exists (select 1 from
> table2 where col2 = t.col1);
> table1 has 40M + rows. if that matters.
>
> OS is FreeBSD 6.2, postgresql version 8.2.6

You'll have to post the actual query and error message.  WHERE NOT EXISTS 
has been supported since version 7.1.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: not exists clause
@ 2008-01-11 04:00  Guy Rouillier <guyr-ml1@burntmail.com>
  parent: S Golly <gollykai@gmail.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Guy Rouillier @ 2008-01-11 04:00 UTC (permalink / raw)
  To: pgsql-performance

S Golly wrote:
> I cannot get the "not exists" clause of ANSI SQL to execute correctly.
> select t.col11, t.col1... from table1 t where not exists (select 1 from 
> table2 where col2 = t.col1);
> table1 has 40M + rows. if that matters.
>  
> OS is FreeBSD 6.2, postgresql version 8.2.6
>  
> Is it not supported or a bug ?
> thank you for your support.

This is really not a performance question, but a general SQL question.

select * from t1

f1
--
1
2
3

select * from t2

f1
--
1
2

select * from t1
where not exists
(
select 1
from t2
where t2.f1 = t1.f1
)

f1
--
3

-- 
Guy Rouillier



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2008-01-11 04:00 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-10 20:15 not exists clause S Golly <gollykai@gmail.com>
2008-01-10 21:50 ` Josh Berkus <josh@agliodbs.com>
2008-01-11 04:00 ` Guy Rouillier <guyr-ml1@burntmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox