Search code examples
liquibase

How to check for a result of >0 in Liquibase precondition (SQL format)?

In a Liquibase precondition, I need to check that the result of a query returns a count greater than 0. I know a precondition can be structured like this to check the result is 0:

-- precondition-sql-check expectedResult:0 SELECT COUNT(*) FROM mytable WHERE columnA LIKE '%foo%'

How do I express that precondition so that the migration only runs if there are values in columnA that contain "foo"?

Something like expectedResult:>0

Note that I need a solution that works with SQL format changesets. There is a <NOT> element for XML and JSON changesets, but the documentation doesn't show any support for it in SQL-formattes changesets.

Solution

  • Looks like Liquibase does not support not for formattedSQL changesets. You can tweak SQL query to match the expected pattern, e.g.:

    --changeset foo:bar
--preConditions onFail="MARK_RAN"
     --precondition-sql-check expectedResult:f SELECT COUNT(*) = 0 FROM my_table;
--/preConditions
--comment "PreCondition works"