Re: Is NULL equal to NULL or not?

On Mon, 9 Dec 2002 jco@cornelius-olsen.dk wrote:

> Hi,
>
> My first message on this list:
>
> Using PostgreSQL 7.1.3.

Stop.  Upgrade right now.  You're asking questions about a version of
postgresql that is quite old now.  I'm not sure I remember using 7.1.3,
but since I started out with 6.5.2, I'm sure I must have.  But it's been a
long long long time since I've used either, and I certainly don't have a
test box laying around running it.  So I can't test anything on my end to
see how it works.

However, on 7.3, select 1 where null=null; gets me a 0 output.  Same goes
for 7.2.3.

> I've noticed that
>         select NULL=NULL;
> yields TRUE. It is also possible to select rows in this manner.

That's NOT because null EQUALS null, but because it allowed you to assign
one null value to another.  which technically can be done, so it came out
true.

> At the same time in PL/pgSQL:
>
>         > drop function demo1();
>         create function demo1() returns boolean as '
>         declare
>           a boolean:=NULL;
>           b boolean:=NULL;
>         begin
>           return a=b;
>         end;
>         ' language 'plpgsql';
>
>         > select demo1();
> yields NULL. Yet if I'd written "return NULL=NULL" I would have gotten the
> same result as in the simple query;

Try a line like this:

return a is null;

and see what you get.  Use the verb is, not the = sign to compared nulls.
NOT is to decide it not null.