You have to distinguish two situations: you compare one COLUMN against NULL, or you compare the whole ROW (RECORD) against NULL.
Consider the following query:
SELECT
id,
txt,
txt IS NULL AS txt_is_null,
NOT txt IS NULL AS not_txt_is_null,
txt IS NOT NULL AS txt_is_not_null
FROM
(VALUES
(1::integer, NULL::text)
)
AS x(id, txt) ;
You get this:
+----+-----+-------------+-----------------+-----------------+
| id | txt | txt_is_null | not_txt_is_null | txt_is_not_null |
+----+-----+-------------+-----------------+-----------------+
| 1 | | t | f | f |
+----+-----+-------------+-----------------+-----------------+
This is, I guess, what you and I would expect. You are checking one COLUMN against NULL, and you get "txt IS NOT NULL" and "NOT txt IS NULL" are equivalent.
However, if you do a different check:
SELECT
id,
txt,
x IS NULL AS x_is_null,
NOT x IS NULL AS not_x_is_null,
x IS NOT NULL AS x_is_not_null
FROM
(VALUES
(1, NULL)
)
AS x(id, txt) ;
Then you get
+----+-----+-----------+---------------+---------------+
| id | txt | x_is_null | not_x_is_null | x_is_not_null |
+----+-----+-----------+---------------+---------------+
| 1 | | f | t | f |
+----+-----+-----------+---------------+---------------+
This may be surprising. One thing looks reasonable (x IS NULL) and (NOT x IS NULL) are the opposite of one another. The other thing (the fact that neither "x IS NULL" nor "x IS NOT NULL" are true), looks weird.
However, this is what the PostgreSQL documentation says that should happen:
If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a row-valued expression that contains both null and non-null fields will return false for both tests. In some cases, it may be preferable to write row IS DISTINCT FROM NULL or row IS NOT DISTINCT FROM NULL, which will simply check whether the overall row value is null without any additional tests on the row fields.
I must confess I don't think I've ever used a row-valued comparison against null, but I guess that if the possibility is there, there might be some use-case for it. I don't think is common, anyhow.
id
in my real codebase, but only after spending few hours searching for a problem.rec_variable IS NOT NULL
is checking if all columns are NOT NULL, whilerec_variable IS NULL
is checking if all columns are NULL. HenceNOT rec_variable IS NULL
gives what I expected - an answer to the question "is there anything inside?".