I am using PostgreSQL 9.3. Suppose we are manufacturing some items of certain types, at any moment we are producing at most one item of a certain type. The following table captures our history of manufactured items, there may be rows where manufactured_until
is null
– those are the items produced currently.
create table item
(
id int,
type_id int,
manufactured_from timestamp,
manufactured_until timestamp
)
Sample data:
1 | 101 | 1.1.2000 | 31.12.2012
2 | 102 | 1.4.2003 | 1.1.2010
3 | 101 | 1.1.2013 |
4 | 102 | 2.1.2010 | 4.5.2014
5 | 102 | 5.5.2014 |
The following logic should hold: for each item type, there should be at most one item being produced at the moment (manufactured IS NULL
). In the example, I should not be able to add the record (6, 101, 27.8.2014, NULL)
.
I'd like to write a UNIQUE
constraint that will guard this. Is it possible? For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type?
Best Answer
A partial unique index should do this:
Look into range types and exclusion constraints. They were specifically designed for this problem.
Something like this (untested):
The inserts will look a bit different as there is only a single column now:
[2000-01-01,2012-12-31]
defines a closed interval that includes both dates.[2013-01-01,)
defines an open interval without an end (maps tomanufactured_until is null
with your current table design)Then add an exclusion constraint to guard the range:
You don't need a unique index any more because the constraint will make sure that nothing will overlap.
More information in the manual: http://www.postgresql.org/docs/current/static/rangetypes.html
If you search the internet for "Postgres exclusion constraints" you will find several presentations and blog posts regarding this topic.
P.S.: it seems that you actually want a
daterange
(instead of a "timestamp range") because you probably don't want to include the time of day with the intervals that define when something was manufactured.