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

Check if a Postgres composite field is null/empty

提问by coderama

With postgres composite typesyou 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:

使用postgres 复合类型，您基本上可以构建一个结构被定义为另一个表的字段。我有一个名为“收件人”的“人”类型的复合字段。在我的特定场景中，此收件人字段通常为空。检查复合字段是否为空的正确方法是什么。我试过：

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 NULLis true in all cases. But there is an exception - composite types. When one field of a composite value is NULLand another field is NOT NULL, then result of both operators is false. IS NULLis true, only when all fields are NULL. IS NOT NULLis true, only when all fields are NOT NULL. For any case in between, then both operators return false.

人们可能认为!(x IS NULL) = x IS NOT NULL在所有情况下都是如此。但有一个例外——复合类型。当复合值的NULL一个字段是NOT NULL，另一个字段是，则两个运算符的结果都为假。IS NULL为真，仅当所有字段都为NULL. IS NOT NULL为真，仅当所有字段都为NOT NULL. 对于介于两者之间的任何情况，两个运算符都返回 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?

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

回答by Mureinik

IS NULLand IS NOT NULLwork for complex types too, so these two should be appropriate:

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

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

回答by Erwin Brandstetter

To catch cases where not allfields of the the compositevalue (row / record) are NULL:

要捕获并非复合值（行/记录）的所有字段都为 NULL 的情况：

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

<row-type> is NULLonly returns TRUEif allfields are NULL.
<row-type> is NOT NULLonly returns TRUEif allfields are NOT NULL.

<row-type> is NULL仅TRUE当所有字段都为时才返回NULL。
<row-type> is NOT NULL仅TRUE当所有字段都为时才返回NOT NULL。

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

括号是可选的。无论如何，运算符优先级都对我们有利。

Testing row / record for NULL

测试行/记录是否为 NULL

Demonstrating the various options:

演示各种选项：

CREATE TEMP TABLE recipient (r text, i int);  -- to register the row type

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)
   ) 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

SQL Fiddle.

SQL小提琴。

Related:

有关的：

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

• 对一组列的 NOT NULL 约束
• 设置变量时，记录的 IS NOT NULL 测试不会返回 TRUE