(x IS NOT NULL) vs (NOT x IS NULL) in PostgreSQL

Question

Why is x IS NOT NULL not equal to NOT x IS NULL?

This code:

CREATE TABLE bug_test (
    id int,
    name text
);

INSERT INTO bug_test
VALUES (1, NULL);

DO $$
DECLARE
    v_bug_test bug_test;
BEGIN
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);

    SELECT *
    INTO v_bug_test
    FROM bug_test
    WHERE id = 1;

    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NULL);
    RAISE NOTICE '%: %', v_bug_test, (v_bug_test IS NOT NULL);
    RAISE NOTICE '%: %', v_bug_test, (NOT v_bug_test IS NULL);
END
$$;

DROP TABLE bug_test;

gives following output:

(,): t
(,): f
(,): f
(1,): f
(1,): f ???
(1,): t

while I would expect to get this output:

(,): t
(,): f
(,): f
(1,): f
(1,): t <<<
(1,): t

Answer 1

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.