PostgreSQL Bitwise operators with bit varying "cannot AND bit strings of different sizes"

Question

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

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.

Why does PostgreSQL choke on this?

How should I rewrite this query to play nicely?

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

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

Answer 1

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.

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

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.

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.

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.