具有位变化的"PostgreSQL位运算符"“不能与不同大小的位串" [英] PostgreSQL Bitwise operators with bit varying "cannot AND bit strings of different sizes"

问题描述

我有一个变化的位掩码字段，我想对它执行按位与运算.

I have a varying bitmask field and I want to perform a bitwise AND on it.

PG::Error: ERROR:  cannot AND bit strings of different sizes
SELECT "groups".* FROM "groups"  WHERE (read_roles_bitmask = B'0' OR read_roles_bitmask & B'10' > B'0')

(您需要在表中使用长度不同的位掩码，以获取此错误.)

( you need to have bitmasks with varying lengths in your table to get this error. )

我期望按位运算看起来像下面这样:00010及以上100000010 = 00010

I'm expecting the bitwise math to look like the following: 00010 & 100000010 = 00010

我还尝试过将位掩码转换为没有运气的整数.

I've also tried casting the bitmask to a integer with no luck.

为什么PostgreSQL对此感到窒息?

Why does PostgreSQL choke on this?

我应该如何重写此查询才能使其正常运行?

How should I rewrite this query to play nicely?

我能够使用以下内容来使按位运算符起作用:lpad(read_roles_bitmask :: varchar，64，'0'):: bigint

I was able to use the following to get bitwise operators working: lpad(read_roles_bitmask::varchar,64,'0')::bigint

但是这仅限于64位，还有更好的方法吗?

However this is limited to 64 bits, is there a better way?

推荐答案

PostgreSQL bit 和 bit different 类型的行为对于拒绝它的方式是完全无济于事的扩展用于操作的位域，并对其进行右扩展以进行强制类型转换，而不是对其进行左扩展.

The behaviour of the PostgreSQL bit and bit varying types is exceedingly unhelpful, with the way it refuses to extend bitfields for operations, and it right-extends them for casts instead of left-extending them.

Pg在与"或或"运算之前将较小的操作数左扩展为零是有意义的，而不是失败.

It would make sense for Pg to left-extend the smaller operand with zeroes before an AND or OR operation, rather than failing.

您不能使用强制转换为 bit(n)来获得相同的长度，因为出于某些疯狂的原因，强制转换为 bit(n) 右键点击该参数，使该参数几乎在所有情况下都没有用.

You can't use a cast to bit(n) to get the same lengths, because for some insane reason a cast to bit(n) right-pads the argument, making it useless in almost all situations.

您可以使用类似 lpad($ 1 :: text，great(length($ 1)，length($ 2))，'0'):: bit variant 之类的方法来向左扩展位字段零到两个长度中的较大者.这很麻烦，但是会起作用.我建议编写包装函数以包含混乱情况.

You can use something like lpad($1::text, greatest(length($1), length($2)),'0')::bit varying to left-extend a bit field with zeroes to the greater of two lengths. It's cumbersome, but it'll work. I'd recommend writing wrapper functions to contain the mess.

或者，考虑修改 src/backend/utils/adt/varbit.c 中的 bit 支持代码，以向左扩展和左截断位字段添加功能和用于进行向左扩展的比较.根据现有代码，这应该很容易.

Alternately, consider modifying the bit support code in src/backend/utils/adt/varbit.c to add functions to left-extend and left-truncate bit fields, and functions to do left-extending comparisons. It should be pretty easy based on the existing code.

这篇关于具有位变化的"PostgreSQL位运算符"“不能与不同大小的位串"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！