From: | Bartosz Dmytrak <bdmytrak(at)gmail(dot)com> |
---|---|
To: | bradford <fingermark(at)gmail(dot)com> |
Cc: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: How do I setup this Exclusion Constraint? |
Date: | 2012-05-01 18:20:21 |
Message-ID: | CAD8_UcasVuEnuP0PwcNRECSK=Np1dxJTXKL5K6M68vZ8jE=2Yw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
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
From | Date | Subject | |
---|---|---|---|
Next Message | Misa Simic | 2012-05-01 18:38:08 | Re: How do I setup this Exclusion Constraint? |
Previous Message | bradford | 2012-05-01 17:39:38 | Re: How do I setup this Exclusion Constraint? |