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.
I have it in mind to restore the feature, but in a different way. The
two columns should be defined separately, not as one, and then the
second of the two gets a flag setting, like:
column range_beg { primary_key: Y; }
column range_end { primary_key: Y; range_from: range_beg; }
The "range_from" setting ties one column to the other and should give me
all the behavior I had without all of the confusion. It would have
three effects:
1) Force range_end >= range_beg
2) Convert the primary key into overlap/nest exclusion
3) Allow a single column foreign key in another table to "know" that it
should do a within match instead of an equality match
begin:vcard
fn:Kenneth Downs
n:Downs;Kenneth
email;internet:ken@xxxxxxxxxx
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard
