From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | Steve Atkins <steve(at)blighty(dot)com> |
Cc: | Postgres-General General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Bit-wise foreign keys |
Date: | 2010-10-01 17:09:23 |
Message-ID: | 29B435AB-60F6-4B2B-A5CC-6A8167E992E3@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 a bit 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 of records of data, among which are some bytes of which each bit marks a certain truth-value. As an internal data-object that's just dandy, but presenting it to, for example a user, or to query it for certain masks without having to delve into the 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 pretty simple 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 ever run into 64-bit bit-fields. My solution won't function as a (foreign key) constraint though, but I didn't really need that anyway.
So far it works quite satisfactory, so I thought others might benefit from the idea. It uses standard functionality that's available 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!
Attachment | Content-Type | Size |
---|---|---|
join-on-bitfields.sql | application/octet-stream | 1.5 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua J. Kugler | 2010-10-01 17:35:38 | Re: Kudos on the 64 bit PostgreSQL for Windows |
Previous Message | novnovice | 2010-10-01 15:40:43 | Re: Merge replication with Postgresql on Windows? |