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?

I reopened the question, since you have sneaked in a second question (we prefer one question per question). This may be of help for the main question (unique and NULL): Multi-column unique constraint and NULL values. And this for the 2nd one (exclusion constraint): Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 27, 2014 at 18:41

score 11 · Accepted Answer · 2014-08-27 07:04:10Z

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.

The exclude constraint requires that you install the extension btree_gist for the included integer. Details: dba.stackexchange.com/questions/37351/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Aug 27, 2014 at 18:38

Stack Exchange Network

Unique constraint to enforce max one null per item

1 Answer 1

Linked

Hot Network Questions

Unique constraint to enforce max one null per item

1 Answer 1

Linked

Related

Hot Network Questions