Search code examples
sqloracle-databasecommentsliquibaseoracle19c

Create column if it doesn't exist using Liquibase

In Liquibase SQL syntax, is it possible to check if a column doesn't already exist? I have seen that theres a columnExists precondition, but couldn't find the opposite of it.

--changeset me:1 runAlways:false runOnChange:false failOnError:true
--precondition-column-exists tableName:my_table columnName:my_col
INSERT COLUMN
Solution

  • You can do something as below:

    --changeset me:1 runAlways:false runOnChange:false failOnError:true
--precondition-sql-check expectedResult:0 SELECT COUNT(*) from information_schema.columns where column_name='COL' and table_name='TABLE'
INSERT COLUMN QUERY HERE

    The above precondition will check if there exists column COL in table TABLE, if the count returned is 0 ( which means the column doesn't exists) then the INSERT COLUMN query will be executed.

    At the moment only the "SQL check" precondition is supported in "formatted SQL" changelogs. Have a look at liquibase docs.

    Preconditions can be specified for each changeset. Currently, only the SQL check precondition is supported.

    --preconditions onFail:HALT onError:HALT
--precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM my_table ```