Modify data type and data in SQL-Server using liquibase

user_7742051 0
Viewed 2051

I have a table temp in SQL server created using liquibase

The liquibase changelog of a table temp is

databaseChangeLog:
  - changeSet:
      author: Author_name
      id: create_temp_table
      createTable:
        tableName: temp
        columns:
          - column:
              name: id
              type: BIGINT
              constraints:
                primaryKey: 'true'
          - column:
              name: type
              type: VARCHAR(255)
          - column:
              name: reference
              type: VARCHAR(255)
          - column:
              name: rate
              type: VARCHAR(255)

I want to modify the rate column from string to decimal. So I create a new changelog file below

databaseChangeLog:
  - changeSet:
      id: modify_column_temp_table
      author: Author_name
      changes:
        - modifyDataType:
            columnName: rate
            newDataType: DECIMAL(18,4)
            tableName: temp

Now, there is already some exising data in the temp table with rate column as string. When the modify_column_temp_table changelog runs, I understand that SQl Server will throw a cast error saying string can't be cat to decimal.

How can I execute a script or command in the same changelog which actually modifies the data of the column along with the data type so that the existing data in the rate is modified to the new data type?

liquibase spring-boot sql-server sql
1 Answers

This is a common situation and it has a well-known decision. You should create a couple of changesets that modify the database step-by-step.

  1. Rename the original column with the new name:
    <changeSet id="rename column" author="authorName" failOnError="true">
        <preConditions>
            <columnExists tableName="temp" columnName="rate"/>
            <!-- if you also wanna check the column type use <sqlCheck> with database-dependent syntax -->
        </preConditions>
        <renameColumn oldColumnName="rate" newColumnName="rate_number" tableName="temp"/>
    </changeSet>
  1. Create column with new datatype:
    <changeSet id="create new column" author="authorName" failOnError="true">
        <preConditions>
            <columnExists tableName="temp" columnName="rate_number"/>
            <not>
                <columnExists tableName="temp" columnName="rate"/>
            </not>
        </preConditions>
        <addColumn tableName="temp">
            <column name="rate" type="NUMBER(38,0)"/>
        </addColumn>
    </changeSet>
  1. Move and convert the data using you database-specific capabilities:
    <changeSet id="copy data" author="authorName" failOnError="true">
        <preConditions>
            <columnExists tableName="rate" columnName="rate"/>
            <columnExists tableName="rate" columnName="rate_number"/>
        </preConditions>
        <sql>
            <!-- your database-specific sql code for copy and transform data -->
        </sql>
    </changeSet>

As you wish you may add <rollback> statement if you want to use rollback capabilities in your workflow. Also, you may add the forth changeset that drops column with name "rate_number" but it may be useful to test the application before removing this column.

The different changeset is required for any DDL statement here because the execution of DDL statement commits the current transaction and it cant be rollback. So you should manually handle the situation when liquibase falls during the update process.

user_105784660
Related