Re: strange IS NULL behaviour

Bruce Momjian <bruce@momjian.us> wrote:

> Is IS DISTINCT FROM correct though?
>
>     SELECT ROW(NULL) IS DISTINCT FROM NULL;
>     ?column?
>     ----------
>     t
>     (1 row)

My recollection from previous discussions is that this is what is
required by the standard.  ROW(NULL) IS NULL, but it is DISTINCT
FROM NULL.  The IS NULL predicate, when applied to a row or record
is meant to indicate whether that row or record *contains only NULL
elements*, and IS NOT NULL is meant to indicate that a row or
record *contains only NOT NULL elements*.  So this is all as
required:

test=# create table x (c1 int, c2 int);
CREATE TABLE
test=# insert into x values (1, 1), (2, null), (null, 3), (null, null);
INSERT 0 4
test=# select * from x where x is not null;
 c1 | c2 
----+----
  1 |  1
(1 row)

test=# select * from x where x is null;
 c1 | c2 
----+----
    |   
(1 row)

test=# select * from x where not x is null;
 c1 | c2 
----+----
  1 |  1
  2 |   
    |  3
(3 rows)

test=# select * from x where not x is not null;
 c1 | c2 
----+----
  2 |   
    |  3
    |   
(3 rows)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company