UNIQUE constraint on a specific pairing I am trying to work out a special UNIQUE constraint: there are many columns in a Users table but only these two are relevant for the question:

id bigint,
active boolean,
...


I want to allow multiple rows to have the same id - but only one of them may have active = true.

E.g. There can be many rows with id = 1 and active = false, but only one can have id = 1 and active = true.
For this, you can make use of a very useful PostgreSQL feature - that of the partial index.

You have your table:

CREATE TABLE foo
(
  id BIGINT NOT NULL,
  --
  -- other fields
  --
  active BOOLEAN NOT NULL
);


Then, you do:

CREATE UNIQUE INDEX ON foo (id) 
WHERE active;  -- if you prefer more clarity, could have "WHERE active = TRUE;"


So, we try it out (see the fiddle here):

--
-- `active` - all false for a given id - works
--
INSERT INTO foo 
VALUES (1, FALSE), (1, FALSE), (1, FALSE); 


check:

SELECT * FROM foo;


Result:

id  active
1   f
1   f
1   f


Another possibility - the specific one pertaining to the question:

--
-- one and only one `active` = true for a given id - works
--
INSERT INTO foo 
VALUES (2, FALSE), (2, FALSE), (2, TRUE);


Check:

SELECT * FROM foo;


Result:

id  active
1   f
1   f
1   f
2   f
2   f
2   t


Final test:

--
--  attempt to INSERT two true `active` values for a given id - fails!
--
INSERT INTO foo
VALUES (3, FALSE), (3, FALSE), (3, TRUE), (3, TRUE);


Result:

ERROR:  duplicate key value violates unique constraint "foo_id_idx"
DETAIL:  Key (id)=(3) already exists.


The foo table is unchanged, which is what we want!