So, you're sayin' I ain't crazy? :)
-----Original Message-----
From: Hoover, Jeffrey [mailto:jhoover@jcvi.org]
Sent: Wednesday, January 28, 2009 12:18 PM
To: Kevin Grittner; Scott Whitney; pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Strange query problem...
Wow! I would never have expected that behavior, but heres the proof:
cameradb_dev=# select * from a1;
i
-------
one
three
five
two
four
(5 rows)
cameradb_dev=# select * from a2;
j | i
---+------
0 |
2 | two
4 | four
(3 rows)
cameradb_dev=# select * from a1 where i not in (select i from a2);
i
---
(0 rows)
cameradb_dev=# select * from a1 where i not in (select coalesce(i,'')
from a2);
i
-------
one
three
five
(3 rows)
cameradb_dev=#
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: Wednesday, January 28, 2009 1:05 PM
To: Scott Whitney; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Strange query problem...
>>> "Scott Whitney" <swhitney@journyx.com> wrote:
> Um. How is this possible?
> mydb=# select * from time_recs where id_time_rec not in (select
> id_time_rec from punch_time_recs);
> (0 rows)
> Table "public.punch_time_recs"
> Column | Type | Modifiers
>
-------------------+------------------------+---------------------------
----
> id_time_rec | character varying(38) |
The column in punch_time_recs is null capable. Try using NOT EXISTS.
The SQL spec requires the NOT IN to be the equivalent of a "not
equals" test for all entries, and you can't say that any given value
is not equal to NULL, since NULL can mean that there is a value but
you don't know it. The semantics of NOT EXISTS are subtly different
here -- it means there aren't any rows known to have the value.
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin