PostgreSQL constraint - column A unique for rows where column B is true

unique_index_with_where.sql

-- Scenario: There should only exist one record for each distinct value for column A that has column B set to true. There can be any number of records for each distinct value for column A where column B is false.
-- i.e. there can be only one active record (column B true) for each distinct value of column A

DROP TABLE IF EXISTS test;

CREATE TABLE test (
  "a" CHARACTER VARYING(3) NOT NULL,
  "b" BOOLEAN NOT NULL DEFAULT TRUE
);

INSERT INTO test (a,b) VALUES ('1', true);
INSERT INTO test (a,b) VALUES ('1', false);
INSERT INTO test (a,b) VALUES ('1', false);
INSERT INTO test (a,b) VALUES ('1', true);

SELECT (
  CASE WHEN COUNT(*) = 4 THEN 'PASS - Inserted without error without constraint' ELSE 'FAIL - Did not insert' END
) AS "result" FROM test;


TRUNCATE TABLE test;

CREATE UNIQUE INDEX ON test (a,b) WHERE b = true;

INSERT INTO test (a,b) VALUES ('1', true);
INSERT INTO test (a,b) VALUES ('1', false);
INSERT INTO test (a,b) VALUES ('1', false);
INSERT INTO test (a,b) VALUES ('1', true);

SELECT (
  CASE WHEN COUNT(*) = 4 THEN 'FAIL - Inserted without error with constraint' ELSE 'PASS - Did not insert with constraint' END
) AS "result" FROM test;