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.

Workout

Why do you want to do a precondition check? In general, you shouldn't need to do that, because Liquibase will keep track of whether the alter table has been run or not. – Kettledrum

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>

Yesima

It'a good idea to check a schema name TABLE_SCHEMA = 'your_table_schema' – Britten

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);

Fisc

Answer 3

I know the question was for Teradata, but principle is the same.

I prefer SQL files, so in changelog I have (for Oracle), is:

<include file="roles.sql" relativeToChangelogFile="true" />

and then in roles.sql

there is

--changeset betlista:2022-01-04_2200-87-insert
--preconditions onFail:MARK_RAN
--precondition-sql-check expectedResult:0 select count(*) from ddh_audit.DDH_USER_ROLE where id = 87;
insert into ddh_audit.DDH_USER_ROLE(id, role_name, description)
values(87, 'CONTAINERS_READONLY', 'Can read Containers reference data');

the query added by David Cram would make the trick.

I do not know and I didn't try if condition could be on multiple lines, I know --rollback can.

Eben