Re: What's wrong with this query?

Thomas Kellerer wrote:
>> 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).

Sorry to be nitpicking, but maybe in that case it adds to clarity:

A comparison with NULL does not return FALSE, but "undefined" or NULL.

Try to run the following queries:

SELECT 1 = 2;
and
SELECT 1 = NULL;

and observe the different result.

In the context of the original question this difference does not matter,
because a comparison is considered successful only if it returns TRUE.

But I think this way it becomes clearer *why* neither = nor != will
succeed for a NULL (= undefined) value: if you don't know which value
a certain thing has, you can neither say that it is equal to 1 nor
that it is not equal to 1.

Yours,
Laurenz Albe