IS NOT NULL test for a record does not return TRUE when variable is set
null, plpgsql, postgresql, row, sql
Solution
I see two possible reasons, why ...
Neither of these raises appear in my messages log
Not logged
Firstly, a `NOTICE` is not normally written to the database log with default settings. The manual:
`log_min_messages` (`enum`)
Controls which message levels are written to the server log. Valid values are `DEBUG5`, `DEBUG4`, `DEBUG3`, `DEBUG2`, `DEBUG1`, `INFO`, `NOTICE`, `WARNING`, `ERROR`, `LOG`, `FATAL`, and `PANIC`. (...) The default is WARNING. Note that `LOG` has a different rank here than in `client_min_messages`.
Bold emphasis mine. Also note the different default (`NOTICE`) for `client_min_messages` (preceding item in the manual).
Invalid test
Secondly, consider how a row expression is evaluated. A test `row_variable IS NULL` returns `TRUE` if (and only if) every single element is `NULL`. Consider:
SELECT (1, NULL) IS NULL AS a -- FALSE
, (1, NULL) IS NOT NULL AS b -- also FALSE!
Both expressions return `FALSE`. In other words, a row (or record) variable `(1, NULL)` is neither `NULL`, nor is it `NOT NULL`. Therefore, both of your tests fail.
sqlfiddle with more details
Related:
- NOT NULL constraint over a set of columns
You can even assign a record variable with NULL (`rec := NULL`), which results in every element being NULL - if the type is a well-known row type. Otherwise, we are dealing with an anonymous record and the structure is undefined and you cannot access elements to begin with. But that's not the case with a `rowtype` like in your example (which is always well-known).
Solution: `FOUND`
What's the correct way to test if you received a row from a `SELECT * INTO`?
You have to consider that the row could be NULL, even if it was assigned. The query could very well have returned a bunch of NULL values (if the table definition in your query allows NULL values).
There is a simple and secure approach. Use `GET DIAGNOSTICS` or, where applicable, the special variable `FOUND`:
SELECT * FROM my_table WHERE owner_id = 6 INTO my_var;
IF NOT FOUND THEN
RAISE NOTICE 'Query did not return a row!';
END IF;
Details in the manual.
Problem
Using a plpgsql procedure to extract a record if it exists, and then if it does, do something with it. The variable is a rowtype: ``` my_var my_table%rowtype; ``` I populate it with a SQL statement: ``` select * from my_table where owner_id = 6 into my_var; ``` I know it definitely has the row: ``` raise notice 'my_var is %', my_var; ``` Returns: ``` NOTICE: my_var is (383,6,10) ``` But now I want to test that it got the record and BOTH of these if conditions fail: ``` if my_var is null then raise notice 'IT IS NULL'; end if; if my_var is not null then raise notice 'IT IS NOT NULL'; end if; ``` Neither of these raises appear in my messages log - it just never enters the blocks. What's the correct way to test if you received a row from a `SELECT * INTO`?