ALTER TABLE ALTER COLUMN cannot run inside a transaction block;

Question

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]

Well, then don't do it inside a transaction. What exactly is your question? — , Feb 07 '20 at 09:38
To be fair to the OP, I came to this post after searching this error because in the past I have run ALTER TABLE commands inside a transaction block without issue, but created a seemingly simple script on another instance that threw this error with no additional detail — Will Ayd, Mar 18 '21 at 17:46

score 1 · Answer 1 · answered Dec 23 '21 at 11:01

1

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 '21 at 11:01

BuzzR

11
1

score 0 · Answer 2 · edited Dec 04 '21 at 20:49

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.

ALTER TABLE ALTER COLUMN cannot run inside a transaction block;

2 Answers2