带有空值的bool_and和bool_or的行为 [英] Behaviour of bool_and and bool_or with NULL values

问题描述

我正在使用聚合函数bool_or和bool_and来聚合一些记录，并查看特定列上是否存在不一致。 根据official documentation：

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？

推荐答案

是，这些聚合似乎忽略了NULL输入。

这种愚蠢的行为几乎肯定直接来自于SQL标准(尽管我不能确定pay $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函数被允许就地更新累加器。如果性能是个问题，并且您愿意/能够build并部署您自己的C模块，那么(与大多数内部函数一样)您可以非常轻松地复制-粘贴bool_and() implementation并根据您的需要对其进行调整。

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

这篇关于带有空值的bool_and和bool_or的行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！