sqlpostgresql

PostgreSQL constraint - only one row can have flag set

I have a PostgreSQL table 

CREATE TABLE my_table
(
  id serial NOT NULL,
  name text,
  actual boolean DEFAULT false,
  CONSTRAINT my_table_pkey PRIMARY KEY (id),
);

How can I set a constraint that only one row can have actual flag set to TRUE?

Solution

  • You can create a unique partial index on that column only for true values:

    create unique index on my_table (actual) 
where actual = true;

    SQLFiddle: http://sqlfiddle.com/#!15/91f62/1