具有 NULL 值的bool_and和bool_or的行为

Question

我正在使用聚合函数bool_or和bool_and来聚合一些记录，并查看特定列是否存在不满。 根据官方文件：

bool_and(expression)    true if all input values are true, otherwise false
bool_or(expression)     true if at least one input value is true, otherwise false

但是，此测试查询：

SELECT bool_or(val),bool_and(val) FROM UNNEST(array[true,NULL]::bool[]) t(val)

生成两列的true。

我认为bool_and排除了NULL价值观。有没有办法使用内置的聚合函数使上述查询返回true和NULL？

Answer 1

出于我需要做的事情的目的，我最终使用了两个额外的列，您可以在更新的测试查询中看到它们：

SELECT
bool_or(val),
bool_and(val),
bool_or(val IS NULL),
bool_and(val IS NULL)
FROM UNNEST(array[true,NULL]::bool[]) t(val)

这足以涵盖本专栏的所有三个值。

请注意，使用COUNT(DISTINCT val)不起作用，因为不包括NULL

我希望这可以帮助某人！

Answer 2

是的，看起来这些聚合忽略了NULL输入。

这种愚蠢几乎可以肯定直接来自SQL标准(尽管我不打算支付200美元来确定(。像sum(var)这样的其他标准聚合以这种方式工作，似乎它们可能只是从那里推断出来，在处理null值时没有考虑算术和布尔运算之间的固有差异。

我认为没有任何方法可以解决它;我相信说服这些函数返回NULL的唯一方法是向它们提供一个空数据集。(顺便说一句，谁坚持零行的sum()应该NULL而不是0应该承诺......

幸运的是，Postgres是无限可扩展的，定义自己的聚合非常简单：

CREATE FUNCTION boolean_and(boolean, boolean) RETURNS boolean AS
'SELECT $1 AND $2'
LANGUAGE SQL IMMUTABLE;
CREATE AGGREGATE sensible_bool_and(boolean)
(
STYPE = boolean,
INITCOND = true,
SFUNC = boolean_and,
-- Optionally, to allow parallelisation:
COMBINEFUNC = boolean_and, 
PARALLEL = SAFE
);

如果您只需要一次性查询，并且不想(或没有权限(向数据库添加新的聚合定义，则可以通过定义并将它们引用为pg_temp.boolean_and()/pg_temp.sensible_bool_and()来将它们放在连接本地临时架构中。
(如果您使用的是连接池，则可能需要在完成后删除它们。

请注意，这比内置bool_and()慢 ~10 倍(尽管在许多实际用例中不太可能成为瓶颈(;SQLboolean值是堆分配且不可变的，因此boolean_and()需要为每次迭代分配一个新值，同时允许LANGUAGE C函数就地更新累加器。如果性能是一个问题，并且您愿意/能够构建和部署自己的 C 模块，那么(与大多数内部函数一样(您可以非常轻松地复制粘贴bool_and()实现并对其进行调整以满足您的需求。

但所有这些都是矫枉过正，除非你真的需要它。在实践中，我可能会选择@Luke的解决方案。

Answer 3

我的回答是：

SELECT bool_or(val),bool_and(val) FROM UNNEST(array[true,NULL]::bool[]) t(val) 
having bool_and (val is not null)

希望对您有所帮助

Answer 4

我认为您可以这样做以获得所需的行为：

SELECT
BOOL_OR(val),
BOOL_AND(COALESCE(val, FALSE)) 
FROM
UNNEST(array[TRUE, NULL]::bool[]) t(val)

Answer 5

我最终做了以下操作，使这样的bool_and返回真，假或空，如果分别所有值都为真，所有值为假，一个值为空：

select
case when count(case when val IS null then 1 end) > 0 
then null 
else bool_and(val) 
end
from unnest(array[true,null]::bool[]) t(val)