modifyDataType does not support running in a transaction in redshift

[stephen-elves]

I’m trying to alter a column datatype in redshift using modifyDataType and running into this error: Unexpected error running Liquibase: ERROR: ALTER TABLE ALTER COLUMN cannot run inside a transaction block [Failed SQL: (0)

See also forum thread: https://forum.liquibase.org/t/modifydatatype-on-a-column-in-redshift/6627

[stephen-elves]

Update: Getting this error when trying to use modifyDataType even with runInTransaction: false

Unexpected error running Liquibase: ERROR: ALTER TABLE ALTER COLUMN does not support USING expression [Failed SQL: (0) ALTER TABLE grandcare_datalake.rs_staging_como_devicemodels_latest ALTER COLUMN url TYPE VARCHAR(max) USING (url::VARCHAR(max))]

[daviibf]

Hello,

I am having the same problem.

Firstly:

runInTransaction: false command doesn't work with redshift.

Secondly:

In redshift, it doesn't support the usual ALTER COLUMN <column_name> TYPE <new_data_type> command from the postgre-sql server.

We should run something like this:

-- Add a new column to the existing table with the correct type you want.
ALTER TABLE <table_name> ADD COLUMN <new_column_name> VARCHAR(100);

-- Then copy all the values from your target column into the new added column.
UPDATE <table_name> SET <new_column_name> = <column_name>;

-- Then drop the old column
ALTER TABLE <table_name> DROP COLUMN <column_name>;

-- Then rename the new column into the old column name
ALTER TABLE <table_name> RENAME COLUMN <new_column_name> TO <column_name>;

However, when running this sequence of commands directly in redshift and then running liquibase --defaultsFile=liquibase.properties.redshift_diff diff-changelog, the liquibase CLI will interpret this query as

ALTER TABLE <table_name> ALTER COLUMN <column_name> TYPE <new_data_type> USING (<column_name>::<new_data_type>);

But Redshift doesn't accept either the USING clause like this, so it raises an ERROR as well. Is there any workaround on this ?

[tati-qalified]

I can confirm that this is still an issue for Liquibase version 4.25.1.

Using runInTransaction=false does work for redshift, but the execution is still blocked because of the error @daviibf mentioned: ERROR: ALTER TABLE ALTER COLUMN does not support USING expression.

We will be leaving this ticket open for the community to fix. If you want to submit a PR, our dev team will support and guide you through the process.

Thank you, Tatiana