RE: Can't use NULL in IN conditional?

Since it's probably a "special case" I'd feel it good software engineering
practice to make that fact explicit anyway.

For example:

  SELECT type_id, code FROM product_types WHERE code IN ('0A', 'F3', '99')
OR code IS NULL;

I don't know how that would affect the speed but it might be the clearest
expression of intent.


Piers Scannell
Systems Engineer, GlobeCast France Telecom
Tel: +44 1707 667 228   Fax: +44 1707 667 206



> -----Original Message-----
> From: pgsql-bugs@postgresql.org [mailto:pgsql-bugs@postgresql.org]
> Sent: 11 December 2000 07:49
> To: pgsql-bugs@postgresql.org
> Subject: [BUGS] Can't use NULL in IN conditional?
>
>
> Charles Tassell (ctassell@isn.net) reports a bug with a severity of 3
> The lower the number the more severe it is.
>
> Short Description
> Can't use NULL in IN conditional?
>
> Long Description
> I don't know if this is a "feature" that comes alogn with
> NULL values, or somethign I'm doing wrong, but I can't seem
> to search for the NULL value via a WHERE xx IN clause.  I've
> re-written the query to use OR and it works fine then, but
> not with the IN clause.  This is with Postgres 7.02 on a
> Linux system (PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled
> by gcc egcs-2.91.66)
>
>
> Sample Code
> CREATE TABLE product_types (
>   type_id serial,
>   code  text
> )
> INSERT INTO product_types (code) VALUES ('0A');
> INSERT INTO product_types (code) VALUES (NULL);
> -- This works
> SELECT type_id, code FROM product_types WHERE code = '0A' OR
> code = NULL;
> -- This doesn't
> SELECT type_id, code FROM product_types WHERE code IN ('OA', NULL);
>
>
>
> No file was uploaded with this report
>