Insert into table column if entry does not exist

Question

In Liquibase I would like to insert values if the values are not already set. With a normal insert I suspect that the inserted value will overwrite the previous value if the value is already there. I want it to ony insert if it does not exist. Can this be done?

Right now I am using the insert as seen below:

<insert tableName="state">
  <column name="name" value="fooFoo"/>
  <column name="enabled" valueBoolean="true"/>
</insert>

Answer 1

The proper way to do this is to use preConditions.

There's an <sqlCheck> preCondition.

sqlCheck

Executes an SQL string and checks the returned value. The SQL must return a single row with a single value. To check numbers of rows, use the “count” SQL function. To check for ranges of values, perform the check in the SQL and return a value that can be easily compared against.

<sqlCheck expectedResult="1">SELECT COUNT(1) FROM pg_tables WHERE TABLENAME = 'myRequiredTable'</sqlCheck>

With it your changeSet will look like this:

<changeSet id="foo" author="bar">
    <preConditions onFail="MARK_RAN">
        <sqlCheck expectedResult="0">
            SELECT COUNT(*) FROM state WHERE name='fooFoo' AND enabled=true;
        </sqlCheck>
    </preConditions>
    <insert tableName="state">
      <column name="name" value="fooFoo"/>
      <column name="enabled" valueBoolean="true"/>
    </insert>
</changeSet>