Kenneth Downs wrote:
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).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.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?Well, maybe one day I'll actually have time to create one...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 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
