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