Re: What's wrong with this query?

Mike Christensen wrote on 22.06.2009 00:10:
> I just tracked down a bug in my software due to an "unexpected" behavior
> in Postgres..  Can someone clarify why this doesn't work (I haven't
> tried it on MSSQL or anything else, so I'm not sure if this is the
> official SQL standard or anything)..
>
> CREATE TABLE test
> (
>   value uuid
> );
>
> INSERT INTO test VALUES ('00000000-0000-0000-0000-000000000000');
> INSERT INTO test VALUES ('11111111-1111-1111-1111-111111111111');
> INSERT INTO test VALUES (null);
>
> select * from test where value != '00000000-0000-0000-0000-000000000000';
>
> What I expect to get is two rows: the
> '11111111-1111-1111-1111-111111111111' row and the null row, as both
> those values are in fact not '00000000-0000-0000-0000-000000000000'.
> However, I only get the first one.
>
That is standard behaviour.
A comparison with a NULL value always returns false (and that is not a Postgres
speciality).

You need to use

select *
from test
where value != '00000000-0000-0000-0000-000000000000'
or value is null;

Thomas