CODEWITHSUNDEEP
springpostgresqljpaliquibase
cezarlamann
cezarlamann

Reputation: 1523

How to correct Postgres sequence, after a database population with liquibase?

I'm working with a project that uses Spring MVC with JPA, Liquibase and Postgres. I have some population scripts (SQL) with States, Cities, Zip codes and Addresses with proper relationship between tables/values.

<changeSet id="10" author="cezar">
    <sql dbms="postgresql"
         endDelimiter=";"
         splitStatements="true"
         stripComments="true">
    INSERT INTO ..... VALUES (.....);
    </sql>
</changeSet>

Well, Everything goes fine and Liquibase actually populates the DB, but trouble happens when I need to add a new Address, and I get some exceptions of Primary-Key violation. I know that I need to fix the sequence, but how do I do that after database population at the application startup?

Thank you in advance!

Upvotes: 1

Views: 1759

Answers (2)

user330315
user330315

Reputation:

You can just add another SQL statement that calls setval() in your changeSet:

<changeSet id="10" author="cezar">
    <sql dbms="postgresql"
         endDelimiter=";"
         splitStatements="true"
         stripComments="true">
    INSERT INTO ..... VALUES (.....);
    SELECT setval('sequence_name', (select max(id_column) from the_table));
    </sql>
</changeSet>

This will however only work correctly if there are no concurrent inserts to the table at the same time

Upvotes: 3

Stephan Hochdoerfer
Stephan Hochdoerfer

Reputation: 114

That is indeed a real problem. We were facing the same problem and ended up added such data in an external script. Not ideal because it runs outside of the Liquibase context but we simply needed a way to "code" a bit of complex logic to generate some data as well as doing some programmatically changes to existing data.

Maybe it would be an idea to generate a PostgreSQL function which does the magic, call the function and delete function after the inserts are done.

Upvotes: 0

Related Questions