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
null - those are the items produced currently.
create table item ( id int, type_id int, manufactured_from timestamp, manufactured_until timestamp )
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?