From: | Sam Mason <sam(at)samason(dot)me(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | disjoint union types |
Date: | 2007-10-09 14:38:59 |
Message-ID: | 20071009143857.GQ10098@samason.me.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I was after opinions as to the best way to lay tables out to get the
effect of a "disjoint union" type (also known as a "tagged union").
When I have to do this at the moment, I'm creating a structure like:
CREATE TABLE circle ( id SERIAL PRIMARY KEY, radius REAL NOT NULL );
CREATE TABLE square ( id SERIAL PRIMARY KEY, sidelen REAL NOT NULL );
CREATE TABLE shapes (
id SERIAL PRIMARY KEY,
tag INTEGER NOT NULL,
circleid INTEGER REFERENCES circle
CHECK ((tag = 1) = (circleid IS NOT NULL)),
squareid INTEGER REFERENCES square
CHECK ((tag = 2) = (squareid IS NOT NULL))
);
I can then put data into this by doing:
BEGIN;
INSERT INTO circle (radius) VALUES (1);
INSERT INTO shapes (tag,circleid) VALUES (1,currval('circle_id_seq'));
COMMIT;
This works, but it's sometimes a bit of a headache turning things around
so they fit this structure. Are there standard solutions to this that
work better?
Thanks,
Sam
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Kelly | 2007-10-09 14:40:59 | Re: DB upgrade |
Previous Message | Scott Marlowe | 2007-10-09 14:36:15 | Re: move whole schema to a new tablespace |