Re: challenging constraint situation - how do I make it - Mailing list pgsql-general

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