Updating Enum Values in PostgreSQL - The Safe and Easy Way


tl;dr:

# rename the existing type
ALTER TYPE status_enum RENAME TO status_enum_old;

# create the new type
CREATE TYPE status_enum AS ENUM('queued', 'running', 'done');

# update the columns to use the new type
ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING job_status::text::status_enum;

# remove the old type
DROP TYPE status_enum_old;

Unfortunately, there is no ALTER TYPE name DROP/RENAME VALUE ... in psql (as of 9.6). The most popular solutions I found for this problem were always one of these two:

"Drop and re-create the type."
This solution alone is no good if the type is already in use as you would have to drop any columns that use the type.

"Modify the system tables with DELETE FROM pg_enum..."
No! hacking the catalogs is dangerous, unpredictable, and almost always a bad idea.

Instead, lets create a new type with the correct enum values and swap out the old one. This way we don't have to drop and recreate columns nor modify system tables.

Example Scenario

CREATE TYPE status_enum AS ENUM('queued', 'waiting', 'running', 'done');

CREATE TABLE job (
  id         int,
  job_status status_enum
);

INSERT INTO job VALUES
  (1, 'queued'),
  (2, 'waiting'),
  (3, 'running'),
  (4, 'done');

Removing a Value

Lets say we want to remove the waiting status and set all waiting jobs to running.

# remove references to the deprecated value
UPDATE job SET job_status = 'running' WHERE job_status = 'waiting';

# rename the existing type
ALTER TYPE status_enum RENAME TO status_enum_old;

# create the new type
CREATE TYPE status_enum AS ENUM('queued', 'running', 'done');

# update the columns to use the new type
ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING status::text::status_enum;

# remove the old type
DROP TYPE status_enum_old;

Updating/Renaming a Value

Lets say we decided blocked was a better description than waiting. In this case we are simply adding a new value (blocked) and removing an existing value (waiting). So, the process is the same as above except we must add our new value to the existing enum first.

# add the new value to the existing type
ALTER TYPE status_enum ADD VALUE 'blocked';

# update references to the deprecated value
UPDATE job SET job_status = 'blocked' WHERE job_status = 'waiting';

# rename the existing type
ALTER TYPE status_enum RENAME TO status_enum_old;

# create the new type
CREATE TYPE status_enum AS ENUM('queued', 'blocked', 'running', 'done');

# update the columns to use the new type
ALTER TABLE job ALTER COLUMN job_status TYPE status_enum USING status::text::status_enum;

# remove the old type
DROP TYPE status_enum_old;

Errors

If you get an error like invalid input value for enum status_enum: "waiting" that means one or more rows have a value (waiting) that is not in your new type. You must handle these rows before you can update the column type.

Mike Moore

Read more posts by this author.

Austin, TX yo1.dog
comments powered by Disqus