2

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>
Adrian
  • 33
  • 2
  • 7

2 Answers2

4

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>
htshame
  • 4,702
  • 3
  • 22
  • 41
  • If you remove the "AND enabled=true" then it becomes better. The logic should be: if "fooFoo" does not exist then add "fooFoo" and "true", else do nothing. – Adrian May 17 '20 at 20:17
0

You can refer to the below question:

MySQL: Insert record if not exists in table

just use < sql > tag to do that in Liquibase.

Hope this helps.

Edit:

< sql >

INSERT INTO state(name, enabled) SELECT * FROM (SELECT 'fooFoo', 'true') AS tmp WHERE NOT EXISTS ( SELECT name FROM state WHERE name = 'fooFoo' ) LIMIT 1

< /sql >

Raushan
  • 151
  • 10
  • I am not sure I understand exactly how to use the tag in Liquibase. Could you write out the whole answer, please? Maybe an explanation to that would be good. – Adrian May 17 '20 at 00:37
  • This doesn't really answer the question - you've given an answer specific to MySQL, but OP hasn't said what database they are using. Presumably, something that works on all databases supported by Liquibase is being requested... – GreyBeardedGeek May 17 '20 at 01:55
  • You literally meant the SQL tag. I saw some other "tag" at another place and thought you meant that. I used your suggestion and modified it. Copy it into your answer and I will mark it as answered. Also give me a thread point, please. INSERT INTO state(name, enabled) SELECT * FROM (SELECT 'fooFoo' AS name, 1 AS enabled) AS tmp WHERE NOT EXISTS ( SELECT name FROM state WHERE name = 'fooFoo') LIMIT 1; And yes, this is a mySQL, which I did not mention. ;) – Adrian May 17 '20 at 02:36
  • Yes, StackOverflow edited out the SQL tag, I had to provide spaces in them to display them. – Raushan May 17 '20 at 13:14