Gabriel Ciubotaru <gciubotaru@bitdefender.com> writes:
> I have a table, with a column of type bit varying for storing
> bit string. This means for me a features list (enabled/disabled).
> Now, if i have an entry with first 2 features only (eg B'11')
> and i need to select all entries which have the 3rd feature enabled, the
> normal syntax for this is :
> SELECT * FROM my_table WHERE features & B'100' = B'100' (just
> test the 3rd bit to be enabled).
> There is a little problem because & cannot operate on different
> length bit string, so we must extend one of the operand (in our case,
> "features" bit string) and the query will be:
> SELECT * FROM my_table WHERE features::bit(3) & B'100' = B'100'.
> Now, the problem is when we have a "feature" with less than 3
> bits. If we have the "feature" = B'11' , after extending it to bit(3) ,
> it will become B'110' (right padding with 0) and the result will be
> B'110' & B'100' = B'100' which is not good.
> If it was left padded , it will become B'011' and the result
> was B'000' which means the "feature" bit is unset.
> Now, my question is, the right padding is intended to work like
> this ? For working in Big Endian (counting bits from left to right) ? Or
> is just wrong coded ?
It's intended to work like that, and documented to work like that;
see the first Note on page
http://www.postgresql.org/docs/9.3/static/datatype-bit.html
I believe that this is compliant with the SQL99 standard, which states
that casting from bit(n) to bit(more-than-n) is done by padding with
zeroes on the right (see 6.22 <cast specification> general rule 11c).
There are other places in the spec where they confusingly refer to
most significant or least significant bits, without clearly saying
which end of the string that means; but "on the right" seems pretty
clear to me.
regards, tom lane