A unique constraint where NULL conflicts with everything

A Constraint Where NULL Conflicts With Everything

Unique constraints in PostgreSQL have well-defined behavior when it comes to NULL values: they treat them as distinct from every other value, including other NULLs. That behavior is usually what you want, but occasionally a request comes along that turns this assumption on its head.

Consider a table with columns a and b:

The required constraint is unusual: if a row has a NULL in b, no other row may exist with the same value for a. But if for a given a there are no rows with NULL in b, then multiple rows are allowed as long as their b values differ.

Why a Standard Unique Constraint Won't Help

A standard unique constraint on (a, b) doesn't work because NULLs are always considered distinct. Neither will a unique index solve the problem easily. Triggers can enforce this logic but introduce race conditions unless you are prepared to run at SERIALIZABLE isolation, which is not an attractive option for most applications.

The root of the difficulty is that NULL is being used to express something it wasn't designed for. The SQL standard defines NULL as “the absence of any data value”, yet goes on to say it is neither equal to nor not equal to any other value. This ambiguity makes NULL a poor way to model the concept of “conflicts with everything”. Using NULL to represent infinity or “any value” is a common abuse—and in PostgreSQL it is unnecessary, since proper Infinity values exist for numeric and date/time types.

Turning the Problem Into a Range

The key insight is that “conflicts with everything” is well expressed by an unbounded range. PostgreSQL has range data types since version 9.2, which represent intervals with inclusive or exclusive bounds—or no bounds at all. For example:

With ranges, we can nail the semantics: instead of storing NULL to mean “every possible value”, we can store a range that is unbounded on both ends. Indeed, the int4range constructor has a convenient property—passing NULL for a bound produces an unbounded range.

Using Exclusion Constraints

Exclusion constraints extend the idea of unique constraints from equality to arbitrary operators. They are backed by GiST indexes. If we map each integer b to a single-element range int4range(b, b, '[]'), the problem becomes one of checking range overlap with the && operator. Column a still needs an equality check, but standard PostgreSQL does not supply GiST operator classes for the scalar = operator.

The btree_gist extension fills that gap by providing B-tree-like operator classes that can work inside GiST indexes:

With that extension in place, the constraint is straightforward:

When b is not NULL, int4range(b, b, '[]') creates a range containing only that integer. When b is NULL, the same function—following the SQL convention—produces a range that is unbounded on both ends, overlapping with every other range in the column.

Verifying the Behavior

The constraint behaves exactly as required:

• Reserve NULL for missing or unknown values, not for “everything” or “infinity”.
• When a unique index is insufficient, look to exclusion constraints with GiST for more expressive rules.
• Range data types are a surprisingly elegant tool for expressing such constraints.