Let’s imagine a business requirement that there should be only one active row in a table. Active means that the column
is_active is set to
true. There can be many rows with the
null values. The row with the only one value can be only one.
The simple table could look like this:
CREATE TABLE test( id SERIAL PRIMARY KEY, is_active BOOLEAN NOT NULL );
I’ve seen so far a solution with a
BEFORE INSERT OR UPDATE trigger, which locks the entire table. I really don’t like explicit locking tables when we can have a better implementation without it.
The Index Solution
One of the nice PostgreSQL features is the vast index creation options. I can create an index for only some rows, something like the below one.
This index ensures that there will be only one row with
is_active = true. There could be many other rows, but only with
null values in that column.
CREATE UNIQUE INDEX ui_active_test ON test(is_active) WHERE is_active;
The Index Description
- This index is made only on the rows where
is_active = true.
- For all those rows (and remember: there should be only one such row)
this index contains only the column
- This index is
UNIQUEso there can only be unique values (in this case: only one row with the value