liquibase cannot cast type enum to enum

user_1562808 0
Viewed 1744

I currently have an enum type in my DB created via liquibase with the following script:

- changeSet:
      id: id_1
      author: my_team
      changes:
        - sql: CREATE TYPE my_team.letters AS ENUM ('A', 'B', 'C')

As I need to add letter D to the enum, I create a new enum 

- changeSet:
      id: id_2
      author: my_team
      changes:
        - sql: CREATE TYPE my_team.letters_2 AS ENUM ('A', 'B', 'C', 'D')

And I update the type

  - changeSet:
      id: id_3
      author: my_team
      changes:
        - modifyDataType:
            columnName: letter
            newDataType: my_team.letters_2
            schemaName: my_team
            tableName: table_name

And I get the following error when executing the Liquibase scripts

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set db/changelog/ddl-my_team-v.0.0.15.my_team::id_3::my_team team:
     Reason: liquibase.exception.DatabaseException: ERROR: cannot cast type my_team.letters to my_team.letters_2
  Position: 89 [Failed SQL: (0) ALTER TABLE my_team.table_name ALTER COLUMN case_status TYPE my_team.letters_2 USING (letter::my_team.letters_2)]

I can't understand why, since the destination type includes all the values of the original one.

Any way this can be done?

Thanks in advance,

liquibase psql enums casting postgresql
3 Answers

I'm not a Postgres expert, but I think the following could do the trick:

  1. Rename your column letter to letter_copy.
  2. Create a new column letter of type letters_2.
  3. Copy all values from letter_copy to letter. Perhaps you'll have to copy values from letter_copy as text like update table_name set letter = letter_copy::text::letters;.
  4. Drop column letter_copy.

Now table_name.letter column should have a type of letters_2 enum with all the values converted from enum letters to enum letters_2.

user_60129390

There is no need to create another enum and jump through hoops to get everything straightened. Just alter the existing enum:

 ALTER letter ADD VALUE 'D' after 'C';
user_76238560

This was failing for me

ALTER TYPE status_type ADD VALUE IF NOT EXISTS 'NEW_ENUM_VALUE';

and below worked

--liquibase formatted sql

ALTER TYPE status_type ADD VALUE IF NOT EXISTS 'NEW_ENUM_VALUE';

I think the reason for the above query to work is that liquibase does not support direct updation of enum types. In order to run query natively have to use --liquibase formatted sql

https://docs.liquibase.com/concepts/changelogs/sql-format.html

user_141560990
Related