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:
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.
FROM pg_tables AS t
WHERE NOT (t IS NOT NULL) AND NOT (t IS NULL)
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
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: