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

            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 $$
        if (new.step_status != 'COMPLETE') or (new.step_status != 'INCOMPLETE')
        from phase_steps where step_type = 'RESEARCH'
            raise exception 'Status value not in range for this phase step';
        end if;
        return new;
$$ 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


  • Please start with your version of Postgres and the exact table definition (CREATE TABLE statement showing data types and constraints). Aug 11, 2021 at 23:26

1 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 (
, 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)
     , ('SURVEY'  , 'ASSIGNED')
     , ('SURVEY'  , 'NOT ASSIGNED')))


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


  • Thanks, Erwin. Your answer(s) always set the Gold Standard! Aug 12, 2021 at 1:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.