Re: strange IS NULL behaviour

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