Re: What's wrong with this query?

On Jun 21, 2009, at 3:37 PM, Thomas Kellerer wrote:

> 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;

Yup.

Or where value is distinct from '00000000-0000-0000-0000-000000000000';

Cheers,
   Steve