在Postgres中将通用位字符串与零进行比较

小编典典

在Postgres中将通用位字符串与零进行比较

sql

有没有一种方法可以在不对位串宽度为0进行硬编码的情况下进行非零位串测试？

例如，假设我有两个表，Users和Features，每个表都带有掩码，我想对此进行测试：

SELECT u.name FROM Users u, Features f
  WHERE u.mask & f.mask;

匹配隐式非零结果。但是，SQL需要一个显式的布尔结果，WHERE而不是隐式的强制转换，例如：

SELECT u.name FROM Users u, Features f
  WHERE (u.mask & f.mask) != 0::BIT(2048);

由于多种原因，我不想2048在此查询中进行硬编码（或其他任何方式）。

测试expr = 0或expr > 0导致类型错误。奇怪的是，我可以测试expr = 0::BIT(1)，但这给出了错误的答案，因为Postgres并不认为所有全零位字符串都相等。

select 0::BIT(2) > 0::BIT(1);
 ?column? 
----------
 t
(1 row)

我可以通过执行以下操作来创建计算出的零：

SELECT u.name FROM Users u, Features f
  WHERE (u.mask & f.mask) != (u.mask & ~u.mask);

哪个可行，但感觉很糟糕。

有什么建议或见解吗？

结果

我对下面提供的几个选项进行了基准测试。感谢您的建议，欧文！

基于一个非常大的数据集和100,000个查询，我发现以下结构导致每秒相关的查询。希望Postgres团队的人看到了这一点，并提供了通用0以加快处理速度！不幸的是，大多数通用方法似乎都导致了字符串转换，这是非常昂贵的。

Constructs                              |  Queries / s
----------------------------------------+--------------
(u.mask & f.mask) <> 0::BIT(2048)       |  158
(u.mask & f.mask) <> (u.mask # u.mask)  |  135
(u.mask & f.mask) <> (u.mask & ~u.mask) |  125
position('1' IN (u.mask & f.mask)) > 0  |   37
(u.mask & f.mask)::TEXT !~ '^0+$'       |   27

阅读 194

2021-04-22

共1个答案

小编典典

短位串

要排除按位AND（&）返回由零组成的位串，但长度可能会更改（B'000...'）的情况，可以使用强制转换为integer（最多bit(32)）或bigint（最多bit(64)）：

SELECT u.name
FROM   users u
JOIN   features f ON (u.mask & f.mask)::int <> 0;

转换为整数时，所有结果都为0。
这也排除了其中任一列为的情况NULL。换句话说，结果必须包括至少一个1。

长位串

如果您的值可以大于64位，则可以将其强制转换text为正则表达式并进行检查：

ON (u.mask & f.mask)::text !~ '^0+$'

模式说明：

^ ..字符串的开头
0+..一个或多个‘0’
$ ..字符串的结尾

或者，如手册所告知：

下面的SQL标准的功能对位串工作，以及字符串：length，bit_length，octet_length，position，substring，overlay。

Ergo：

ON position('1' IN (u.mask & f.mask)) > 0

2021-04-22