Unique constraint to enforce max one null per item
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 &amp;&amp;);


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.