So, you're sayin' I ain't crazy? :) -----Original Message----- From: Hoover, Jeffrey [mailto:jhoo...@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-ow...@postgresql.org [mailto:pgsql-admin-ow...@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" <swhit...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin