Limit Column to a Single NULL Value

A unique index disallows duplicate values from being inserted into the indexed columns. But this does not apply to NULL values.

Here is a simple trick to enforce a rule that a column only contain one NULL value using a partial index and function (in this case, just a constant):

				
					yugabyte=> CREATE TABLE some_table(c1 INT);
CREATE TABLE

yugabyte=> CREATE UNIQUE INDEX ON some_table ((1)) WHERE c1 IS NULL;
CREATE INDEX

yugabyte=> INSERT INTO some_table SELECT NULL;
INSERT 0 1

yugabyte=> INSERT INTO some_table SELECT NULL;
ERROR:  duplicate key value violates unique constraint "some_table_expr_idx"

Have Fun!