Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();

version

PostgreSQL 10.23 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-15), 64-bit

SELECT 1

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

CREATE TABLE

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

CREATE INDEX

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

INSERT 0 3

SELECT * FROM foo;

id
active

1
f

1
f

1
f

SELECT 3

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

INSERT 0 3

SELECT * FROM foo;

id
active

1
f

1
f

1
f

2
f

2
f

2
t

SELECT 6

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

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