How to Ensure There is One Row With a Value

published at 27 Jun, 2011 by Szymon LipiƄski tags: database postgresql programming

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 value true).