Re: What's wrong with this query? - Mailing list pgsql-general

From Albe Laurenz
Subject Re: What's wrong with this query?
Date
Msg-id D960CB61B694CF459DCFB4B0128514C202FF666B@exadv11.host.magwien.gv.at
Whole thread Raw
In response to Re: What's wrong with this query?  (Thomas Kellerer <spam_eater@gmx.net>)
Responses Re: What's wrong with this query?
List pgsql-general
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

pgsql-general by date:

Previous
From: "Albe Laurenz"
Date:
Subject: Re: where is the table?
Next
From: Ivan Sergio Borgonovo
Date:
Subject: Re: temp tables, sessions, pgpool and disk