使用
postgres composite types,您可以基本构建一个将结构定义为另一个表的字段.我有一个类型为“person”的复合字段称为“收件人”.在我的具体情况下,此收件人字段通常为空.检查复合字段是否为空的正确方法是什么？我试过了：
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
在所有这些情况下,它不返回任何东西.那么如何正确地检查一个复合值是否为空？
UPDATE
经过一些阅读,看起来这是我的问题：
One may think that
!(x IS NULL) = x IS NOT NULLis true in all cases. But there is an exception – composite types. When one field of a composite value isNULLand another field isNOT NULL,then result of both operators is false.IS NULLis true,only when all fields areNULL.IS NOT NULLis true,only when all fields areNOT NULL. For any case in between,then both operators return false.
我确实有一些字段为null,而其他的那些不是.我希望该字段被认为是非NULL,如果复合字段中的任何项不为null,则不是当它们都不为空时.除了检查每个领域,是否有任何办法？
解决方法
IS NULL和IS NOT NULL也适用于复杂类型,所以这两个应该是适当的：
select * from bla where recipient is not null select * from bla where recipient is null
