How to Ensure There is One Row With a Value

by Szymon LipiƄski

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