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.

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. — SteveDonie, May 01 '18 at 16:43

htshame · Answer 1 · 2018-05-10T12:09:46.590

7

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>

edited May 10 '18 at 12:09

answered May 10 '18 at 08:07

htshame

6,599
5
36
56

It'a good idea to check a schema name `TABLE_SCHEMA = 'your_table_schema'` – Sergey Nemchinov Jul 03 '23 at 06:03

score 1 · Answer 2 · answered May 10 '18 at 14:19

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

score 0 · Answer 3 · answered Nov 01 '22 at 11:41

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.

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

3 Answers3