disjoint union types

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

Responses

Browse pgsql-general by date

  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