From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, Andres Freund <andres(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Hannu Krosing <hannu(at)2ndquadrant(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Andrew Dunstan <andrew(at)dunslane(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: strange IS NULL behaviour |
Date: | 2013-09-10 20:02:37 |
Message-ID: | 1378843357.75410.YahooMailNeo@web162906.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Bruce Momjian <bruce(at)momjian(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2013-09-10 20:27:32 | Re: getting rid of maintainer-check |
Previous Message | Bruce Momjian | 2013-09-10 19:52:00 | Re: strange IS NULL behaviour |