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

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.

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.