Check if a Postgres composite field is null/empty

coderama picture coderama · Mar 31, 2014 · Viewed 50k times · Source

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?

Answer

Mureinik picture Mureinik · Mar 31, 2014

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