With most types you deal with in SQL and more specifically PostgreSQL, when you use a condition
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);
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.