Column constraint based on values in another column

Question

I have a PostgreSQL table phase_steps, with the following example rows:

phase_step_id|step_type|step_status|
-------------+---------+-----------+
            1| RESEARCH|           |
            2|   SURVEY|           |
        

Update values to the step_status column depend on what value the step_type value is.
When step_type is 'RESEARCH', only values of 'COMPLETE' or 'INCOMPLETE' can be entered for step_status values.
When step_type is 'SURVEY', only values of 'ASSIGNED' or 'NOT ASSIGNED' can be entered for step_status values.

I tried to manage the 'RESEARCH' step_status constraints with this procedure:

create or replace function insert_step_status_value() returns trigger as $$
    begin
        if (new.step_status != 'COMPLETE') or (new.step_status != 'INCOMPLETE')
        from phase_steps where step_type = 'RESEARCH'
        then
            raise exception 'Status value not in range for this phase step';
        end if;
        return new;
    end;
$$ language plpgsql;

create trigger check_step_status_value before update on phase_steps
for each row execute procedure insert_step_status_value();

However, an insert like

update jobs.phase_steps
set step_status_lu = 'INCOMPLETE'
where phase_step_id = 1;

gives an error:

SQL Error [P0001]: ERROR: Status value not in range for this phase step
Where: PL/pgSQL function insert_step_status_value() line 6 at RAISE

Thoughts?

Answer 1

A CHECK constraint should do the job. Simpler, cheaper and more reliable than any trigger solution:

To only enforce the listed combinatiuons and still allow anything else, your table definition could look like this:

CREATE TABLE jobs.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 = 'RESEARCH' AND step_status IN ('COMPLETE', 'INCOMPLETE')
         OR step_type = 'SURVEY'   AND step_status IN ('ASSIGNED', 'NOT ASSIGNED'))
);

db<>fiddle here

Operator precedence works in our favor, so no extra parentheses are required.
Allows no other values for step_type ans step_status.

The manual:

There are two ways to define constraints: table constraints and column constraints. A column constraint is defined as part of a column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column.

This still allows null values in both columns. A CHECK constraint is passed if the expression yields true or null. You'd have to define where to exclude or allow those. Maybe you simply want both columns NOT NULL?

To also disallow any other value for step_type:

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)

Now any other value for step_type (incl. null) reaches the ELSE branch and makes the check false. (But NULL in step_status still passes.)

db<>fiddle here