Check if a Postgres composite field is null/empty

Question

With postgres composite types you can basically build a field with the structure being defined as another table. I have the composite field called "recipient" of type "person". This recipient field is often left empty in my specific scenario. What is the correct way to check if a composite field is empty. I tried:

select * from bla where recipient is not null
select * from bla where recipient is null
select * from bla where recipient = null
select * from bla where recipient != null

In all of these cases, it doesn't return anything. So how do you correctly check if a composite value is empty or not?

UPDATE

After some more reading, it looks like this is my problem:

One may think that !(x IS NULL) = x IS NOT NULL is true in all cases. But there is an exception - composite types. When one field of a composite value is NULL and another field is NOT NULL, then result of both operators is false. IS NULL is true, only when all fields are NULL. IS NOT NULL is true, only when all fields are NOT NULL. For any case in between, then both operators return false.

I do have some fields that are null, and others that are not. I was hoping that the field would be considered to be NOT NULL, if any item in the composite field is not null... not when ALL of them are not null. Is there any way around this other than checking each field?

Solution 1

IS NULL and IS NOT NULL work for complex types too, so these two should be appropriate:

select * from bla where recipient is not null
select * from bla where recipient is null

Solution 2

To catch cases where not all fields of the the composite value (row / record) are NULL:

SELECT *
FROM   bla
WHERE  NOT (recipient IS NULL);

<row-type> is NULL only returns TRUE if all fields are NULL.
<row-type> is NOT NULL only returns TRUE if all fields are NOT NULL.

Parentheses are optional. Operator precedence works in our favor anyway.

Demo

CREATE TYPE recipient AS (r text, i int);

SELECT recipient
     , recipient IS NULL         AS all_null
     , recipient IS NOT NULL     AS all_notnull
     , NOT recipient IS NULL     AS some_notnull
     , NOT recipient IS NOT NULL AS some_null
FROM  (
   VALUES
    (('foo', 1   )::recipient)
  , ((NULL , 2   )::recipient)
  , ((NULL , NULL)::recipient)
  , (NULL)
   ) AS tbl(recipient);

Result:

 recipient | all_null | all_notnull | some_notnull | some_null
-----------+----------+-------------+--------------+-----------
 (foo,1)   | f        | t           | t            | f
 (,2)      | f        | f           | t            | t
 (,)       | t        | f           | f            | t
 null      | t        | f           | f            | t

db<>fiddle here
_sqlfiddle

Related:

• NOT NULL constraint over a set of columns
• IS NOT NULL test for a record does not return TRUE when variable is set