sql - 检查 Postgres 复合字段是否为空/空

Question

与 postgres composite types您基本上可以构建一个字段，其结构被定义为另一个表。我有一个名为“收件人”的复合字段，类型为“人”。在我的特定情况下，此收件人字段通常留空。检查复合字段是否为空的正确方法是什么。我试过:

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

在所有这些情况下，它不会返回任何内容。那么如何正确判断复合值是否为空呢？

更新

经过更多阅读，看来这是我的问题:

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.

我确实有一些字段为空，而另一些则不是。我希望如果复合字段中的任何项目不为空，则该字段将被视为 NOT NULL……而不是当所有项目都不为空时。除了检查每个字段之外，还有其他解决方法吗？

Answer 1

IS NULL 和 IS NOT NULL 也适用于复杂类型，所以这两个应该是合适的:

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