Re: Bitmask trickiness

On Wed, Jul 21, 2010 at 4:41 AM, Howard Rogers <hjr@diznix.com> wrote:
> On Wed, Jul 21, 2010 at 6:08 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Tue, Jul 20, 2010 at 9:59 PM, Howard Rogers <hjr@diznix.com> wrote:
>>> Suppose 1=Red, 2=Yellow, 4=Green and 8=Orange.
>>>
>>> Now suppose the following data structures and rows exist:
>>>
>>> create table coloursample (recid integer, colour integer, descript varchar);
>>> insert into coloursample values (1,2,'Yellow only');
>>> insert into coloursample values (2,10,'Yellow and Orange');
>>> insert into coloursample values (3,11,'Red, Yellow and Orange');
>>> insert into coloursample values (4,12,'Green and Orange');
>>> insert into coloursample values (5,13,'Red, Green and Orange');
>>
>> Is there a reason you're avoiding bit strings?
>> http://www.postgresql.org/docs/8.3/interactive/datatype-bit.html
>> http://www.postgresql.org/docs/8.3/interactive/functions-bitstring.html
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
> Why on Earth would I want to store this sort of stuff in a bit string?!

Because you are manipulating bits and not integers?  I guess there are
10 kinds of people, those who like think in binary and those who
don't.

> I don't know about you, but I find looking at 21205 a darn'd site
> easier than staring blankly at 101001011010101!!

If the fifth bit means one thing, and the 7th bit means something
else, quick which of the following have the fifth bit set and the 7th
bit off:

01001101 (base2)
or
77 (base 10)


> And, fundamentally,
> they mean precisely the same thing.

Of course.  But that wasn't my point, and by my example above, one is
much easier to figure out than the other if you're interested in bit
twiddling.

> And the '&' function works as
> nicely with boring old decimals as it does with long-winded binaries,
> so I really don't see the point of making it more complicated than it
> needs to be -but I'm open to be enlightened on the matter!

I fail to see how storing a binary as a binary and showing it as a
binary makes things more complicated.  But I'm open to someone showing
me how that's true.  At least hex or octal have direct and simple
conversions where each hex or octal digit represents 4 or 3 bits
respectively.  Decimal does not.

> Thanks for the second link though. I hadn't realised that PostgreSQL
> was so richly-endowed with bitwise functions. Specifically, it's got
> the bitwise XOR I was thinking it would be nice to have in these sorts
> of situations:
>
> ims=# select * from coloursample where colour # 10 = 0;
>  recid | colour |     descript
> -------+--------+-------------------
>     1 |     10 | Yellow and Orange
> (1 row)
>
> Not quite sure how to apply that to my more realistic example just
> yet, but I think this will be very helpful, so thank you!

Note you can cast integer to bitstring, but there may be some odd
behaviour for sign bits and such.  Which is again why I'd use the
right type for the job, bit string.  But it's your project.