Postgres中与零进行通用位串比较

Question

Postgres中与零进行通用位串比较

6

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

例如，假设我有两个表，用户和功能，每个表都有掩码，我想进行以下测试：

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);

这个方法虽然可行，但感觉像是一个糟糕的黑客攻击。

有什么建议或见解吗？

结果

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

根据非常大的数据集和 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

- Joseph

非零比特字符串测试是什么？而你所说的 匹配隐式非零结果 意味着什么？能否解释一下或者举一些例子说明哪些情况会通过测试，哪些情况则不会？你的列是否定义为 NOT NULL？请提供你的表定义（可以在 psql 中使用 \d users 命令）。另外，请问你使用的是哪个版本的 Postgres？ - Erwin Brandstetter

我在寻找一个非零的结果。例如，（B'101'＆B'100'）！= 0 :: BIT（3），而（B'101'＆B'010'）= 0 :: BIT（3）。由于我正在使用字段（u.mask＆f.mask），所以我不想在查询中硬编码BIT长度，以便在模式中需要扩展BIT字符串时可以轻松更改应用程序中的许多查询。 - Joseph

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

短位字符串

为了排除按位与 (&) 返回的仅由零组成的位字符串的情况，但长度可能会改变（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。

因此：

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