Re: Is NULL equal to NULL or not?

jco(at)cornelius-olsen(dot)dk wrote:
>
> Hi,
>
> My first message on this list:
>
> Using PostgreSQL 7.1.3.
>
> I've noticed that
> select NULL=NULL;
> yields TRUE. It is also possible to select rows in this manner.

Not generally since 7.2:

http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/release-7-2.html

"In this release, comparisons using = NULL will always return
false (or NULL, more precisely). Previous releases automatically
transformed this syntax to IS NULL. The old behavior can be
re-enabled using a postgresql.conf parameter."

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

Not sure why that is. Different sub-parsing paths, perhaps?
However, the default has changed since the old days (7.1). The
hack was in place because ancient versions of Access (re: Access
95) used to call into the ODBC driver with parameters which
resulted in PostgreSQL getting queried with:

SELECT
...
WHERE field = NULL;

That behavior changed in Microsoft products a long time ago, so
the PostgreSQL behavior now mirrors the SQL standard. So from
this point forward, I'd code using IS NULL for NULL comparisons.
And I'd upgrade... ;-)

Hope that helps,

Mike Mascari

P.S.: NULLs are evil.