16

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
  • 1
    Are you considering the fact that you are actually checking a whole record against NULL. (You are – joanolo Dec 9 '16 at 15:49
  • @joanolo Yes. I have switched the code to check for id in my real codebase, but only after spending few hours searching for a problem. – Anil Dec 9 '16 at 15:51
  • 1
    It seems to me that rec_variable IS NOT NULL is checking if all columns are NOT NULL, while rec_variable IS NULL is checking if all columns are NULL. Hence NOT rec_variable IS NULL gives what I expected - an answer to the question "is there anything inside?". – Anil Dec 9 '16 at 15:54
17

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.

  • Yes, the explanation makes sense and matches the results of the experiments I did since posting this. Why I compared the whole record variable is because my background is in non-SQL languages, where this is quite common. Regarding use cases, I thing this is handy when one wants to check if all fields in a record variable are filled (rec IS NOT NULL), instead of doing it field by field. – Anil Dec 9 '16 at 16:30
  • 1
    @Anil: Exactly the use case you mention has popped up before: stackoverflow.com/questions/21021102/… – Erwin Brandstetter Dec 10 '16 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.