| 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: | Whole Thread | Raw Message | 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 |