Composite types and NULL in PostgreSQL

Basics

-- a row including only nulls is null
$ SELECT ROW(NULL, NULL) IS NULL;
-> true

-- a row including no nulls is not null
$ SELECT ROW(10, 10) IS NOT NULL;
-> true

Unusual

-- this row is not NULL
$ SELECT ROW(10, NULL) IS NULL;
-> false

-- but it's also not NOT NULL
$ SELECT ROW(10, NULL) IS NOT NULL;
-> false

-- there is a difference between `NOT ROW(...) IS NULL` and `ROW(...) IS NOT NULL`
$ SELECT NOT ROW(10, NULL) IS NULL;
-> true

Wait, what?

-- as mentioned above, a row including only nulls is null
$ SELECT ROW(NULL) IS NULL;
-> true

-- this does not apply recursively, though
$ SELECT ROW(ROW(NULL)) IS NULL;
-> false

What's going on there?

Values inside of a composite type are checked for NULL value equality, which is not the same as recursively checking with IS NULL!

This behavior is explained in this twitter post.

We can (ab)use this to check if a value is literally NULL or just a value that IS NULL:

SELECT  value              AS value,
        value      IS NULL AS is_null,
        ROW(value) IS NULL AS is_null_value
FROM    (VALUES (NULL), (ROW(NULL))) AS x(value);

 value  | is_null | is_null_value
--------+---------+---------------
 <null> | t       | t
 ()     | t       | f

Check out this blog post for more information on NULL behavior.