Liquibase + Postgresql + Spring Jpa : Id auto increment issue

Question

I have the following Id description in the entity:

@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
private Long id;

Liquibase instruction for generate this id is following :

  <column name="id" autoIncrement="true"  type="INT">
     <constraints nullable="false" primaryKey="true" primaryKeyName="pk_entity"/>
  </column>

Also I have liquibase scripts that insert to this table predefined values, e.g.

    <insert tableName="entityTable" schemaName="public">
        <column name="id">1</column>    
        <!- other fields-->
    </insert>

The problem has appeared when I try to insert a new record without id using Jpa repository. I got an error with a message like "duplicate id". So, I understand that jpa(hibernate) doesn't use postgresql sequence for getting a new id value. And I don't want to include the sequence name to the entity's id description. I have hoped that this situation could be resolved by the postgresql itself. And I wouldn't to use the 'hibernate_sequence'. So, any idea how I can resolve this issue. Thank you.

Answer 1

Liquibase's instruction autoIncrement="true" generates serial column for PostgreSQL. For serial column PostgreSQL will create a sequence with a name like tablename_colname_seq. Default column values will be assigned from this sequence.

But when you explicitly insert a value into serial column, it doesn't affect sequence generator, and its next value will not change. So it can generate a duplicate value, which is exactly your case.

To prevent this after you inserted explicit values you need to change the current value of a sequence generator either with ALTER SEQUENCE statement or with setval() function, e.g.:

ALTER SEQUENCE tablename_colname_seq RESTART WITH 42;

SELECT setval('tablename_colname_seq', (SELECT max(colname) FROM tablename));

This should fix the issue.