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 isNULL
and another field isNOT NULL
, then result of both operators is false.IS NULL
is true, only when all fields areNULL
.IS NOT NULL
is true, only when all fields areNOT 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?
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