How to Ensure There is One Row With a Value
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 false
or 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 false
or 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
is_active
. - This index is
UNIQUE
so there can only be unique values (in this case: only one row with the valuetrue
).