Re: challenging constraint situation - how do I make it

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