PostgreSQL Enum Type – Unsafe Use of New Value

constraintenumpostgresql

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.

Best Answer

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.

Related Solutions

MySQL unique constraint across two columns

I have modified ypercube's solution a little bit, so that both source and destination are not null, as is guaranteed by the original design. My CHECK constraints are commented out, as they apparently will not work in MySQL. I kept them as comments because they document my intent, and they will work on other RDBMS.

CREATE TABLE PointType
( PointTypeID tinyint unsigned NOT NULL,
  TypeDescription CHAR(20) NOT NULL,
  PRIMARY KEY (PointTypeID),
  UNIQUE (TypeDescription)
) ;

INSERT INTO PointType 
  (PointTypeID, TypeDescription)
VALUES
  (1, 'Source'), 
  (2, 'Destination') ;

CREATE TABLE PointUsageQuota
( RouteID int unsigned NOT NULL,
  PointTypeID tinyint unsigned NOT NULL,
  PointID smallint(5) unsigned NOT NULL,
  PRIMARY KEY (PointID),     -- and this is what all the fuss is about
  UNIQUE (RouteID , PointID, PointTypeID), -- target for the foreign keys
  FOREIGN KEY (PointTypeID)
    REFERENCES PointType (PointTypeID)
) ;

CREATE TABLE Route
( RouteID int unsigned NOT NULL,
  SourcePointID smallint(5) unsigned NOT NULL,
  SourceTypeID tinyint unsigned NOT NULL, 
      -- CHECK(SourceTypeID = 1),
  FOREIGN KEY (RouteID , SourcePointID, SourceTypeID) 
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  DestinationPointID smallint(5) unsigned NOT NULL,
  DestinationTypeID tinyint unsigned NOT NULL, 
      -- CHECK(DestinationTypeID = 2),
  FOREIGN KEY (RouteID , DestinationPointID, DestinationTypeID) 
    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
  --  other fields
  PRIMARY KEY (RouteID)
) ;

Testing:

insert into PointUsageQuota
values(1,1,666),(1,2,354);

INSERT INTO Route VALUES (1, 666, 1, 354, 2);
-- this fails:
INSERT INTO Route VALUES (2, 666, 1, 354, 2);
-- this fails too:
INSERT INTO Route VALUES (2, 354, 1, 666, 2);

PostgreSQL – Why Generate an Already Used PK Value?

PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does.

It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing. The first one is easy to fix:

SELECT setval('your_sequence_name', (SELECT max(id) FROM your_table));

The second one means debugging.

Django (or any other popular framework) doesn't reset sequences on its own - otherwise we would have similar questions every other day.

Best Answer

Related Solutions

MySQL unique constraint across two columns

PostgreSQL – Why Generate an Already Used PK Value?

Related Question