Search code examples
postgresqlliquibasejdbctemplate

liquibase + jdbctemplate update statement - autoincrement issue

In liquibase changset I am inserting some record to database

<changeSet id="test" author="xyz">
        <insert tableName="testtable">
            <column name="id" value="1"/>
            <column name="name" value="testdata"/>
        </insert>
</changeSet>

Then using jdbcTemplate I am trying to insert new rows using instance update() method.

   jdbcTemplate.update(
        "INSERT INTO test.testtable(name) VALUES (?)",
        new Object[] {
          someObject.getName()
        });

When running above method for the first time, I am getting error that record with this ID already exists in the table. However when I repeat operation , I succeed with incremented ID with value 2.

How to integrate liquibase and jdbcTemplate together to eliminate this problem? I would expect that jdbcTemplate will somehow recognize that this ID 1 is already occupied and insert data with incremented, non-conflicting, unique ID.

I am using postgres.

Is there any option to do it without removing liquibase entry with hardcoded ID value?

Solution

  • I would expect that jdbcTemplate will somehow recognize that this ID 1 is already occupied and insert data with incremented, non-conflicting, unique ID.

    This would not happen. Liquibase looks to see if the changeset has been ran in the environment (by checking to see if the changeset id is present in the databasechangelog table). If it is not present, it will run the changeset. Just that simple, Liquibase is not doing any thinking for you, you have to make sure that the insert will work in any reasonable condition.

    I did like the suggestion in the comment above to not insert an id but instead use an auto-increment if you are wanting to always insert and have the id increment.