PostgreSQL constraint - only one row can have flag set

Question

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?

Answer 1

You can create a unique 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