sql - Postgres 中的通用位串与零的比较

Question

有没有办法在不硬编码位串宽度为 0 的情况下进行非零位串测试？

例如，假设我有两个表，Users 和 Features，每个表都有掩码，我想测试一下:

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

匹配隐式非零结果。但是，SQL 要求 WHERE 的显式 bool 结果而不是隐式转换，例如:

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

Answer 1

短位串

排除 bitwise 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'
$ .. 字符串结尾

或者，作为 the manual informs :

The following SQL-standard functions work on bit strings as well as character strings: length, bit_length, octet_length, position, substring, overlay.

因此:

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