Composite data types why IS NOT NULL is not the same as NOT IS NULL

With most types you deal with in SQL and more specifically PostgreSQL, when you use a condition such as something IS NULL and it returns false, you can be assured that something IS NOT NULL would return true. This is not the case with composite types.

Composite types are types that can contain multiple attributes. A classical case is a table row type that exists for all tables in PostgreSQL. If you have a value something that is a composite type, for example a row of a table, something IS NOT NULL is only true if none of the attributes of the composite are NULL.

Let's see this in action with a built in table in PostgreSQL

SELECT  COUNT(1) AS total, 
    COUNT(1) FILTER (WHERE t IS NULL) AS tnull, 
    COUNT(1) FILTER (WHERE t IS NOT NULL) AS tisnotnull,
    COUNT(1) FILTER (WHERE NOT (t is NULL) ) AS tnotisnull
FROM pg_tables AS t;

Now intuition might be telling you, if the total number of records in pg_tables is 62 and none of them are NULL, then certainly none of them should be IS NOT NULL. You might also be thinking that asking for NOT (something IS NULL) is just another way of asking something IS NOT NULL. The output instead yields something somewhat surprising:

 total | tnull | tisnotnull | tnotisnull
 -------+-------+------------+------------
    62 |     0 |         10 |         62
(1 row)

The other take-aways from this exercise is if you want the anti-thesis of IS NULL, you really want to use NOT (something IS NULL) and not (something IS NOT NULL); using something IS NOT NULL is a terse way of checking if all the fields in your record are filled in.

So taking a peak at some of the records that violate common-sense of IS NOT NULL.

SELECT  *
FROM pg_tables AS t
WHERE NOT (t IS NOT NULL) AND NOT (t IS NULL)
limit 2;

They all have at least one attribute that IS NULL, in this case the tablespace.

 schemaname |  tablename   | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+--------------+------------+------------+------------+----------+-------------+-------------
 pg_catalog | pg_statistic | postgres   | NULL       | t          | f        | f           | f
 pg_catalog | pg_type      | postgres   | NULL       | t          | f        | f           | f
(2 rows)

The 10 records that don't violate IS NOT NULL, all have everything filled in

If a composite has all attributes that are NULL, then it is considered to be NULL. You can test this out yourself with this query:

SELECT ROW(NULL::name, NULL::name, NULL::name, NULL::name, NULL::boolean, 
	NULL::boolean, NULL::boolean, NULL::boolean)::pg_tables IS NULL;

SELECT NULL::pg_tables IS NULL;

Both examples above yield true though they have vastly different representations.