By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE ProjectCreationTasks (
Id text NOT NULL PRIMARY KEY,
ProjectName text,
ProjectCode text,
DenialReason text,
CONSTRAINT my_constraint CHECK
( (ProjectName IS NULL AND ProjectCode IS NULL AND DenialReason IS NULL)
OR ((ProjectName IS NOT NULL AND ProjectCode IS NOT NULL) AND DenialReason IS NULL)
OR (DenialReason IS NOT NULL AND ProjectName IS NULL AND ProjectCode IS NULL)
)
);
CREATE TABLE
TRUNCATE TABLE ProjectCreationTasks
TRUNCATE TABLE
-- if Name/Code has a value then Reason must be null
INSERT INTO ProjectCreationTasks
VALUES (1, '1', NULL, NULL)
ERROR: new row for relation "projectcreationtasks" violates check constraint "my_constraint" DETAIL: Failing row contains (1, 1, null, null).
-- Name/Code/Reason can all be null simultaneously,
INSERT INTO ProjectCreationTasks
VALUES (2, NULL, NULL, NULL)
INSERT 0 1
-- if Reason has a value then Name/Code must be null
INSERT INTO ProjectCreationTasks
VALUES (3, NULL, NULL, 1)
INSERT 0 1
INSERT INTO ProjectCreationTasks
VALUES (4, 1, NULL, 1)
ERROR: new row for relation "projectcreationtasks" violates check constraint "my_constraint" DETAIL: Failing row contains (4, 1, null, 1).