Re: How do I setup this Exclusion Constraint? - Mailing list pgsql-general

I played with this problem few months ago and found out that mulitidimentional cube could be a solution (http://www.postgresql.org/docs/9.1/static/cube.html).

If You have col1 and date1, date2  then Your cube is a simple line in 2 dimensional space - axis: col1, date (line between points X, Y1 and X, Y2),

if you have col1, col2 and date1 and date2 then Your cube is in 3 dimensional space (axis: col1, col2, date), and so on.

You have to be sure that those cubes (lines even points!) are separete, eg. distance is greater then 0 - this really depends on requirements: is it possible that date ranges stick together, like continuous period of time divided into 2? if Yes then distance could be 0 but intersection is still 0 You have to think about this.

You can build GIST index on cube function to be sure that exclusion check is fast.

For sure this is not the only one solution, maybe others will find more easy way - I am really interested in simpler solution.

regards,
Bartek