Postgres 16

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 phase_steps (
  phase_step_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, step_type     text
, step_status   text
, CONSTRAINT    step_status_for_step_type CHECK (
       (step_type, step_status)
   IN (('RESEARCH', 'COMPLETE')
     , ('RESEARCH', 'INCOMPLETE')
     , ('SURVEY'  , 'ASSIGNED')
     , ('SURVEY'  , 'NOT ASSIGNED')))
/* equivalent:
, CONSTRAINT    step_status_for_step_type CHECK (
      step_type = 'RESEARCH' AND step_status IN ('COMPLETE', 'INCOMPLETE')
   OR step_type = 'SURVEY'   AND step_status IN ('ASSIGNED', 'NOT ASSIGNED')
      )
*/
);

CREATE TABLE

-- legal as declared
INSERT INTO phase_steps(step_type, step_status) VALUES ('RESEARCH','COMPLETE'), ('SURVEY', 'NOT ASSIGNED');

INSERT 0 2

-- null in any of the two columns
INSERT INTO phase_steps(step_type, step_status) VALUES ('RESEARCH', null);
INSERT INTO phase_steps(step_type, step_status) VALUES (null, 'ASSIGNED');

INSERT 0 1

INSERT 0 1

-- But different combinations don't pass
INSERT INTO phase_steps(step_type, step_status) VALUES ('foo', 'bar');

ERROR:  new row for relation "phase_steps" violates check constraint "step_status_for_step_type"
DETAIL:  Failing row contains (5, foo, bar).

-- restart with more restritive CHECK constraint
DELETE FROM phase_steps;

ALTER TABLE phase_steps
  DROP CONSTRAINT step_status_for_step_type
, ADD  CONSTRAINT step_status_for_step_type CHECK (
         CASE step_type WHEN 'RESEARCH' THEN step_status IN ('COMPLETE', 'INCOMPLETE')
                        WHEN 'SURVEY'   THEN step_status IN ('ASSIGNED', 'NOT ASSIGNED')
                        ELSE false END);  -- !!

INSERT INTO phase_steps(step_type, step_status) VALUES  ('RESEARCH','COMPLETE'), ('SURVEY', 'NOT ASSIGNED');

DELETE 4

ALTER TABLE

INSERT 0 2

-- null for step_status still passes
INSERT INTO phase_steps(step_type, step_status) VALUES ('RESEARCH', null);

INSERT 0 1

-- But anyother step_type (incl. null) is ruled out now :
INSERT INTO phase_steps(step_type, step_status) VALUES ('foo', 'ASSIGNED');

ERROR:  new row for relation "phase_steps" violates check constraint "step_status_for_step_type"
DETAIL:  Failing row contains (9, foo, ASSIGNED).

INSERT INTO phase_steps(step_type, step_status) VALUES ('foo', 'bar');

ERROR:  new row for relation "phase_steps" violates check constraint "step_status_for_step_type"
DETAIL:  Failing row contains (10, foo, bar).

INSERT INTO phase_steps(step_type, step_status) VALUES ('COMPLETE', 'ASSIGNED')

ERROR:  new row for relation "phase_steps" violates check constraint "step_status_for_step_type"
DETAIL:  Failing row contains (11, COMPLETE, ASSIGNED).

INSERT INTO phase_steps(step_type, step_status) VALUES (null, 'ASSIGNED');

ERROR:  new row for relation "phase_steps" violates check constraint "step_status_for_step_type"
DETAIL:  Failing row contains (12, null, ASSIGNED).

INSERT INTO phase_steps(step_type, step_status) VALUES (null, 'foo');

ERROR:  new row for relation "phase_steps" violates check constraint "step_status_for_step_type"
DETAIL:  Failing row contains (13, null, foo).

INSERT INTO phase_steps(step_type, step_status) VALUES (null, null);

ERROR:  new row for relation "phase_steps" violates check constraint "step_status_for_step_type"
DETAIL:  Failing row contains (14, null, null).