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?

This is a weird limitation. I'd expect Pg's & to left-extend the smaller operand with zeroes automatically. — Craig Ringer
– Craig Ringer, Commented Dec 4, 2012 at 0:48
@Craig: The "must be the same size" behavior is documented. Also, B'10'::bit(1) is B'1' and B'1'::bit(2) is B'10' so bits go left-to-right. I get the impression that the bit order changed somewhere along the line so perhaps they force you to be explicit to avoid version issues. — mu is too short
– mu is too short, Commented Dec 4, 2012 at 0:52
It's documented, but frankly it's pretty useless to right-extend bit fields and offer no left-extend option either implicitly or explicitly. Who uses left-to-right bit strings? — Craig Ringer
– Craig Ringer, Commented Dec 4, 2012 at 0:57
With the read_roles_bitmask & B'10' > B'0' part, you just want to see if bit-2 (from right to left, 1-based counting) is 1, right? If your bits when left-to-right then you could cast to bit(2) to grab just the 2 left-most bits. — mu is too short
– mu is too short, Commented Dec 4, 2012 at 0:58

Craig Ringer · Accepted Answer · 2012-12-04 01:02:42Z

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.

harmic · Accepted Answer · 2015-08-26 02:09:39Z

I had a similar issue today. I wanted to do almost exactly the same thing: mask off the least significant two bits of a bitstring and compare the result with a literal value, like this:

status & b'11' > b'01'

(status was my bit varying column).

Initially I tried using Craig's solution, but it got pretty messy pretty quickly because not only does the mask have to be left extended, so does the value I was comparing the result with, since according to postgresql:

t2=> select b'0010' < b'01';
 ?column?
----------
 t
(1 row)

The RHS is right padded to make it the same size as the LHS before applying the < operation.

In the end I solved it like this:

(status << length(status)-2)::bit(2) > b'01'

The nice thing about that is it allows you to extract any set of bits for comparison. For example to get the pair of bits 3rd from the left:

(status << length(status)-6)::bit(2)

You can also use substring to extract an arbitrary set of bits for comparison.

weinerk · Accepted Answer · 2019-11-18 17:58:35Z

1) as mentioned in other answers -

postgres has has some inconvenient/counterintuitive behaviors - 
right padding when casting to bit(n), 
bitwise ops only on similar size,
etc.

2) one workaround is -

double-casting every value - to integer and then to bit(XX)

pros:

- left vs right padding works correctly 
- all the bit-strings have same length for correct bitwise operations
- comparisons work correctly
- bit-masking/casting to get least significant bits

examples:

basic left padding:

select B'0010'::int::bit(22)
0000000000000000000010

bitwise ops:

select B'0010'::int::bit(22) | B'01'::int::bit(22)
0000000000000000000011

comparison:

select B'0010'::int::bit(22) > B'01'::int::bit(22)
true

bit-masking/casting to get three LEAST significant bits:

select B'11010'::int::bit(3)
010

bit-masking/casting to get three MOST significant bits:

select B'11010'::bit(3)
110

UPDATE: use int8 to accommodate longer bit-strings

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related