Re: strange IS NULL behaviour

Kevin Grittner <kgrittn@ymail.com>

From: Kevin Grittner <kgrittn@ymail.com>

To: Bruce Momjian <bruce@momjian.us>, Robert Haas <robertmhaas@gmail.com>

Cc: Andres Freund <andres@2ndquadrant.com>, Tom Lane <tgl@sss.pgh.pa.us>, Hannu Krosing <hannu@2ndquadrant.com>, Peter Eisentraut <peter_e@gmx.net>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>

Date: 2013-09-10T19:48:08Z

Lists: pgsql-hackers

Bruce Momjian <bruce@momjian.us> wrote:

> FYI, I think these queries below prove that NOT NULL constraints do not
> follow the single-depth ROW NULL inspection rule that PL/pgSQL follows,
> and that my patch was trying to promote for queries:
>
>     CREATE TABLE test2(x test NOT NULL);
>     CREATE TABLE
>     INSERT INTO test2 VALUES (null);
>     ERROR:  null value in column "x" violates not-null constraint
>     DETAIL:  Failing row contains (null).
> -->    INSERT INTO test2 VALUES (row(null));
>     INSERT 0 1

If I remember correctly, the standard wants a NOT NULL constraint
on a column with a composite type to behave the same as

  CHECK (col IS DISTINCT FROM NULL)

... which is consistent with the behavior you show.

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