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!
join-on-bitfields.sql
Description: Binary data
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!
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
