Unique constraint to enforce max one null per item

Question

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?

Answer 1

A partial unique index should do this:

create unique index max_one_null 
   on item (type_id) 
where manufactured_until is null;

For bonus points, is there a reasonably complex way to guard that the intervals do not overlap for one item type

Look into range types and exclusion constraints. They were specifically designed for this problem.

Something like this (untested):

create table item
(
  id int,
  type_id int,
  manufactured_during tsrange
);

The inserts will look a bit different as there is only a single column now:

insert into item 
  (id, type_id, manufactured_during)
values 
  (1, 101, '[2000-01-01,2012-12-31]'),
  (1, 101, '[2013-01-01,)');

[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 to manufactured_until is null with your current table design)

Then add an exclusion constraint to guard the range:

alter table item
  add constraint check_manufactured_range
  exclude using gist (type_id with =, manufactured_during with &&);

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.