postgresql null 计算结果为 false 的情况

case when null evaluates to false

提问by Clodoaldo Neto

Today I was surprised by this casebehaviour:

今天我对这种case行为感到惊讶：

select case when null then true else false end;
 case 
------
 f

I would expect it to return nullsince a nullcasted to boolean yelds a nullnot a false:

我希望它能够返回，null因为 anull转换为布尔值 a nullnot a false：

select null::boolean is null;
 ?column? 
----------
 t

Any comments on the rationale of this behaviour? What Am I missing?

对这种行为的理由有何评论？我错过了什么？

采纳答案by Craig Ringer

You're thinking of the CASEexpression like it was taking the nullas input to a function or operator, where null input generally results in null output:

您正在考虑将CASE表达式null作为函数或运算符的输入，其中空输入通常导致空输出：

regress=> SELECT 't'::boolean = NULL::boolean;
 bool 
------

(1 row)

wheras in fact it behaves like a WHEREclause in terms of null handling:

事实上，它WHERE在空处理方面的行为就像一个子句：

craig=> SELECT 't' WHERE NULL;
 ?column? 
----------
(0 rows)

In WHEREclauses - and in CASE, a NULLresult from a test expression is treated as "not true and therefore false". In some ways it's regrettable that the SQL standard didn't make a NULLresult in a WHEREexpression an error instead of treating it as false, but that's how it is.

在WHERE子句 - 和 in 中CASE，NULL来自测试表达式的结果被视为“不为真，因此为假”。在某些方面，令人遗憾的是 SQL 标准没有NULL将WHERE表达式中的结果视为错误而不是将其视为错误，但事实就是如此。

This is yet another painful symptom of NULL's split personality, where the SQL spec can't decide if NULLmeans "unknown/undefined value" or "the absence of a value", much like the horrible mess with NULLs and aggregates.

这是NULL人格分裂的另一个痛苦症状，其中 SQL 规范无法确定NULL是“未知/未定义值”还是“缺少值”，就像NULLs 和聚合的可怕混乱一样。

回答by tsunllly

Use something like

使用类似的东西

case when p.parent_id is null then false else true end as has_parent

回答by Thorsten Niehues

In case you (also) need something like

如果你（也）需要类似的东西

if column is null then 'value' 

Use:

用：

COALESCE(column_name, 'replacment for null value') as column_name

In case you still need a case statement then use:

如果您仍然需要 case 语句，请使用：

case COALESCE(column_name, 'asdf') 
when 'asdf' then true
else false
end as desired_column_name

回答by Grigory K

You can use 'select' to check DATA column for null:

您可以使用“选择”来检查 DATA 列是否为空：

select 
  ID,
  case (select 1 where DATA is null)
    when 1 then 'no data'
    else data
  end
from ...

回答by EmCo

As the PostgreSQL documentationstates:

正如PostgreSQL 文档所述：

If no WHEN conditionis true then the value of the case expression is the resultin the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is null.

如果没有 WHEN条件为真，则 case 表达式的值是ELSE 子句中的结果。如果省略 ELSE 子句并且没有条件匹配，则结果为空。

Postgresql does not cast the output, and since you have an elsecondition, you're getting false. The next query returns a null value (Since there's no elsecondition)

Postgresql 不会转换输出，并且由于您else有条件，因此您将获得false. 下一个查询返回空值（因为没有else条件）

select case when null then true end;

回答by Igor Romanchenko

The CASEstatement you wrote has two branches:

CASE你写的语句有两个分支：

The one, when null then true, will never happen (because nullis not equal to true)

一，when null then true将永远不会发生（因为null不等于true）

And elsebranch, that will happen when there are no matches in regular whenbranches.

和else分支，当常规when分支中没有匹配项时就会发生这种情况。

Example:

例子：

CASE WHEN val = 1 THEN 5
     WHEN val = 2 THEN 10
     ELSE 20 /*all other values, including null*/
END