Active sql transaction 7 error alter type add cannot run inside a transaction block

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

asked Nov 5, 2018 at 6:39

Workaround for earlier versions of PostgreSQL shown here:

Note this will require special permissions because it changes a system table.

• Replace 'NEW_ENUM_VALUE' with the value you want.
• Replace'type_egais_units' with the oid of the enum you want to change. (Use SELECT * FROM pg_enum to find the enum you want to update, in my case it was a 5-digit number like '19969')

The statement:

INSERT INTO pg_enum (
    enumtypid, 
    enumlabel, 
    enumsortorder
)
SELECT 
    'type_egais_units'::regtype::oid, 
    'NEW_ENUM_VALUE', 
    (SELECT MAX(enumsortorder) + 1 FROM pg_enum WHERE enumtypid = 'type_egais_units'::regtype)

Of course , upgrading PostgreSQL as suggested in the accepted answer, is probably the best.

Does anyone know how to avoid using transactions when running queries from pgAdmin Version 3.5? (i.e. when executing with F5?)

answered Apr 28, 2019 at 3:23

Specifically how to do this with node-pg-migrate is to disable transactions for the migration:

exports.up = (pgm) => {
  pgm.noTransaction()
  pgm.addTypeValue('foo', 'BAR', { ifNotExists: true })
}

answered Jun 3, 2021 at 7:53

answered Oct 26, 2022 at 12:52

I am trying to alter column in redshift from varchar(30) to varchar(100) Its not working and throwing an error as below:

Command used:

alter table t_name alter column c1 type varchar(300);

Error:

[Amazon](500310) Invalid operation: ALTER TABLE ALTER COLUMN cannot run inside a transaction block; [SQL State=25001, DB Errorcode=500310]

This Helped me

set autocommit on;
alter table schemaname.tablename alter column columnname type varchar(2000);
set autocommit off;

Solution Found Here

answered Dec 23, 2021 at 11:01

From AWS Documentation, check if you are violating any conditions:

ALTER COLUMN column_name TYPE new_data_type — A clause that changes the
size of a column defined as a VARCHAR data type. Consider the
following limitations:

• You can’t alter a column with compression encodings BYTEDICT,
  RUNLENGTH, TEXT255, or TEXT32K.

• You can’t decrease the size less than maximum size of existing data.

• You can’t alter columns with default values.

• You can’t alter columns with UNIQUE, PRIMARY KEY, or FOREIGN KEY.

• You can’t alter columns within a transaction block (BEGIN ... END).
  For more information about transactions, see Serializable isolation.