Re: Bit-wise foreign keys - Mailing list pgsql-general

From Alban Hertroys
Subject Re: Bit-wise foreign keys
Date
Msg-id 29B435AB-60F6-4B2B-A5CC-6A8167E992E3@solfertje.student.utwente.nl
Whole thread Raw
In response to Re: Bit-wise foreign keys  (Steve Atkins <steve@blighty.com>)
List pgsql-general
On 20 Sep 2010, at 19:25, Steve Atkins wrote:
> On Sep 20, 2010, at 10:06 AM, Alban Hertroys wrote:
>> Hey all,
>>
>> I'm tossing an idea around again, namely using bit positions and values as foreign key references. Let's start with
abit of background information: 
>>
>> I'm currently parsing a log-file that I want to apply all kinds of statistical analysis to. This file contains lines
ofrecords of data, among which are some bytes of which each bit marks a certain truth-value. As an internal data-object
that'sjust dandy, but presenting it to, for example a user, or to query it for certain masks without having to delve
intothe definition of that particular bit-field it would be great to have a textual representation of each bit. 

(...)

> Or references a single foreign value, if you have a reference table with all the valid bit combinations, which'd be
prettysimple to generate programatically for small numbers of combinations. 
>
>  insert into foo (k integer, v text[]) values (0x21, '{"EARTH","GREEN"}';
>
> You could also apply any other set of constraints you wanted in that way (Fire is Red, Water is either Blue or
Green).


(...)

I managed to find a solution that doesn't involve having n! rows for n bits, which would be a bit problematic if you
everrun into 64-bit bit-fields. My solution won't function as a (foreign key) constraint though, but I didn't really
needthat anyway. 

So far it works quite satisfactory, so I thought others might benefit from the idea. It uses standard functionality
that'savailable in every basic Postgres installation since around 8.3 I think (I use 8.4 since a couple of days now). 

Attached is what I did (you can source the below through psql).


!DSPAM:737,4ca615d7678303570290129!


Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.



!DSPAM:737,4ca615d7678303570290129!

Attachment

pgsql-general by date:

Previous
From: Michal Politowski
Date:
Subject: Why would a scan take so long?
Next
From: "Joshua J. Kugler"
Date:
Subject: Re: Kudos on the 64 bit PostgreSQL for Windows