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?

• Erwin Brandstetter: Please start with your version of Postgres and the exact table definition (CREATE TABLE statement showing data types and constraints).

Answer 1

A CHECK constraint does the job. Simpler, cheaper and more reliable than a trigger.

To enforce the listed combinations, 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')
 )
);

Or a simpler equivalent:

, CONSTRAINT step_status_for_step_type CHECK (
 (step_type, step_status)
 IN (('RESEARCH', 'COMPLETE')
 , ('RESEARCH', 'INCOMPLETE')
 , ('SURVEY' , 'ASSIGNED')
 , ('SURVEY' , 'NOT ASSIGNED')))

fiddle

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.

Operator precedence works in our favor, no additional parentheses required.
Allows no other combined values for (step_type, step_status) - except for null values. A CHECK constraint is passed if the expression evaluates to true or null.

You may want to add a NOT NULL constraint to step_type, then step_status can still be null, and step_type can be anything (but null) in this case. But if step_status has a value, the combination has to pass the check.

Or, 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.)

fiddle

• Rudy Stricklan: Thanks, Erwin. Your answer(s) always set the Gold Standard!