Re: Bitmask trickiness - Mailing list pgsql-general

On Thu, Jul 22, 2010 at 3:09 PM, Howard Rogers <hjr@diznix.com> wrote:
> On Fri, Jul 23, 2010 at 12:35 AM, Peter Hunsberger
> <peter.hunsberger@gmail.com> wrote:
>> On Wed, Jul 21, 2010 at 11:13 PM, Howard Rogers <hjr@diznix.com> wrote:
>>>
>>> On Thu, Jul 22, 2010 at 1:02 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>>
>>>
>>> >>
>>> >> 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!!
>>
>> <snip> lots of stuff</snip>
>>
>>> >
>>> > 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.
>>> >
>>>
>>> Quoting...
>>>
>>> > 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.
>>>
>>> Er, no. 21205 is not an integer. It's an encoded bit of magic.
>>>
>>
>> In that case your database design is fundamentally broken.  A database
>> should have content fields that map to the needs of the application.
>> As you describe your application requirements, that is a bit string
>> and not an integer.  Use bit strings and your application logic is
>> transparent, obvious and easy to maintain.  Use integers and you have
>> to resort to "magic".  As you say, it's your choice, but you came here
>> looking for advice and the advice you were given is very good....
>>
>> --
>> Peter Hunsberger
>
> Hi Peter:
>
> It wasn't, as the original poster pointed out, 'advice' that was given
> so much as personal preference. Had someone said, 'ah, but you see
> storing your 15 meanings in decimal uses up 5 bytes, whereas a
> bitstring only requires 15 bits, and over 10,000,000 records, the
> saving of 3 bytes per record adds up...', then that would be technical
> advice I could listen to, assess and make a call on.

You do realize the first page I linked to told you that, right?  It's
not a particularly big page.  I had made the erroneous assumption
you'd read the link I posted.

> But simply saying "your design is broken... wooooo!" might well scare
> the children, but doesn't really do anything for me, because I know
> for a certainty that it's not broken at all.

I asked if there was a reason you were avoiding bit strings.  Hardly a
"your design is broken" point.  You've now said why you are not using
the type that was designed to handle bit strings for bit strings.

I personally would store them as bit strings and change representation
for users.  There are some issues that come up if your bit strings are
long enough to get close to the last bit in an integer (also mentioned
on the links I posted that didn't get read).  But other than that it
should work fine.