PostgreSQL Bitwise operators with bit varying "cannot AND bit strings of different sizes"
bit-manipulation, postgresql
Solution
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.
Problem
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?