Liquibase preconditions: How do I check for a column being the correct data type?

Question

I have a db upgrade script to change some datatypes on a few columns. I want to do a preCondition check, and call ALTER TABLE only when it is a DECIMAL datatype, but I will want it to be changed to INTEGER.

Couldn't find a predefined precondition for this, and could not write an sqlCheck either.

Answer 1

There's no built-in precondition for column's dataType in liquibase. You may just check whether the column exists or not. If it's already of the datatype you need, no error will be thrown.

OR

You can use sqlCheck in your preconditions and it'll be something like this:

<preConditions onFail="MARK_RAN">
    <not>
        <sqlCheck expectedResult="DECIMAL">
            SELECT DATA_TYPE
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'your_table_name'
            AND COLUMN_NAME = 'your_column_name'
        </sqlCheck>
    </not>
</preConditions>

Answer 2

Another answer already mentions how to do a sqlcheck. However, the actual SQL for Teradata would be something different.

In Teradata you would use a query similar to the following and expect the columnType='D' for decimal values

Select ColumnType 
From DBC.ColumnsV 
Where databasename='yourdatabasename' 
and tablename='yourtablename' 
and columnname='yourcolumnname';

You could also do something like this if you want a more human readable column type instead of a type code:

Select Type(tablename.columnname);