Postgres: Unsafe use of new value of enum type

I want to alter a constraint after I have added a new value to an enum I use. So I originally have an enum called activity_state where I want to add a new value. But I also need to change the constraint I put on the field completed_at.

`DO $ BEGIN
    CREATE TYPE activity_state AS ENUM ('TODO', 'SCHEDULED', 'DONE');
  EXCEPTION
    WHEN duplicate_object THEN null;
  END $;

  CREATE TABLE IF NOT EXISTS ${activitiesTable} (
    ...
    state activity_state NOT NULL,
    completed_at timestamptz CHECK (
         (completed_at IS NULL AND state != 'DONE')
      OR (completed_at IS NOT NULL AND state = 'DONE')
    ),
  );

  -- Alter state enum
  ALTER TYPE activity_state ADD VALUE IF NOT EXISTS 'DONE_BY_PASSED';

  -- Alter constrain on
  ALTER TABLE ${activitiesTable} DROP CONSTRAINT maintenance_activities_check;
  ALTER TABLE ${activitiesTable} ADD CONSTRAINT maintenance_activities_check CHECK (
         (completed_at IS NULL AND (state != 'DONE' OR state != 'DONE_BY_PASSED'))
      OR (completed_at IS NOT NULL AND (state = 'DONE' OR state = 'DONE_BY_PASSED'))
    );

The enum seems to be added correctly, but once I use it my new check constraint I get the error:
error: unsafe use of new value "DONE_BY_PASSED" of enum type activity_state

Not sure how I can alter my constrain once the enum is changed? I am using postgres 12.

You have forgotten to show us the hint you got:
ERROR: unsafe use of new value "DONE_BY_PASSED" of enum type activity_state
HINT: New enum values must be committed before they can be used.


You also forgot to mention that you ran the ALTER TYPE and the ALTER TABLE statements in the same transaction.

Like the hint says, you have to commit the ALTER TYPE before you can use the new enum value.

StackExchange Database Administrators Q#280371, answer score: 4