Bruce Momjian <br...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
