Re: challenging constraint situation - how do I make it
От | Kenneth Downs |
---|---|
Тема | Re: challenging constraint situation - how do I make it |
Дата | |
Msg-id | 44745304.5070907@secdat.com обсуждение исходный текст |
Ответ на | Re: challenging constraint situation - how do I make it (Alban Hertroys <alban@magproductions.nl>) |
Ответы |
Re: challenging constraint situation - how do I make it
("Florian G. Pflug" <fgp@phlo.org>)
Re: challenging constraint situation - how do I make it (Alban Hertroys <alban@magproductions.nl>) |
Список | 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
Вложения
В списке pgsql-general по дате отправления:
Предыдущее
От: Richard HuxtonДата:
Сообщение: Re: compiling source code!!!!!!!!!!!!!!!!!!!!!!!!!!!!!