Kenneth Downs wrote:
Alban Hertroys wrote:
Kenneth Downs wrote:
Alban Hertroys wrote:
When encountering this problem I usually wonder why there isn't a
data type that can store a timestamp and can be used to create a
UNIQUE INDEX over it's values. That'd be wonderful.
Well, maybe one day I'll actually have time to create one...
I tried this at trigger level. The real bear is in the fact that
there are two columns, not one. It is trivial to write an exclusion
constraint that disallows overlapping (including nested) values.
What was hard was determining the meta-data structure, how do you
have two columns that are sometimes treated as one and sometimes as two?
Are you refering to a 'timespan' data type that can be determined to
be unique?
I can see some problems there, as both value and range matter; it'd be
similar to determining the uniqueness of an area in a rectangle
(though 1 dimensional only, of course).
I've never really dug into this, so I don't know what possibilities
PostgreSQL offers in this field. Basing this on faith :)
Yes.
I use a heavily dictionary-based toolset. I write out database specs in
CSS-like syntax and it diff's and builds the databases and writes all
triggers, indexes and so forth.
The approach I tried was to have a "range" or "interval" type. You
place a column into a table named "resv_date" or whatever and it would
expand the definition into two columns, you'd get resv_date_beg and
resv_date_end. If you declared the "resv_date" column a primary key
column, it would build trigger code to detect overlaps and nesting and
reject those.
As I said, defining behavior and implementing it was not hard. I even
had foreign keys into ranges that were "smart". If the foreign key was
a single column instead of two, it would satisfy RI if the single value
was between the interval values in the parent table.
The problem comes from the split-personality of the "resv_date" column.
Sometimes its one column, sometimes its two. This made writing the
tools nasty and difficult, and I scratched it and (gasp!) did some
validation in client code.
You could define a new datatype containing two timestamps. From 8.1
onwards you can use such a composite type as a field, I believe (but I
haven't checked it).
You can use pgsql functions to define operators for your type - but
you'll have to use the default input/output functions for composite
types I guess, because those can only be codec in C AFAIK.
greetings, Florian Pflug
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
