error: ALTER TYPE ... ADD cannot run inside a transaction block

Question

I am trying to add new type value to my existing types in PostgreSQL. But I get the following error

error: ALTER TYPE ... ADD cannot run inside a transaction block

The query I used to add a new value to the type is

ALTER TYPE public.request_type ADD VALUE "Check";

I am actually running above query in migrations file which is created using node-pg-migrate

Here public is my schema.

Any idea why this is failing?

Edit:

The below query executes fine when execute it in pgadmin

ALTER TYPE public.request_type ADD VALUE "Check";

But when I run above command through node-pg-migrate migrations it fails and throws above error

Answer 1

As it was mentioned above you can't edit enum within transaction block. But you can create the new one. Here are the steps:

1. Change type from request_type to varchar for all columns/tables which use this type:

ALTER TABLE table_name ALTER COLUMN request_type TYPE VARCHAR(255);

2. Drop and create again request_type enum:

DROP TYPE IF EXISTS request_type;
CREATE TYPE request_type AS ENUM ('OLD_VALUE_1', 'OLD_VALUE_2', 'NEW_VALUE_1', 'NEW_VALUE_2'); 

3. Revert type from varchar to request_type for all columns/tables (revert step one):

ALTER TABLE table_name ALTER COLUMN request_type TYPE request_type USING (request_type::request_type);