Hibernate: Selecting a Primary Key’s Generation Type

Recently we’ve been working on a new Application that we develop in using HSQLDB, but intend to run on PostgreSQL.

So today I ran into a problem with one of my entities but only when I run it onĀ  POSTGRESQL. Here’s the stack trace:

org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "pk_trade_item_ownership"

After digging I found out what the problem is:

There’s a sequence on the table that we defined with a current value of 1 but there’s test data already inserted into that database with an id of 1


  • First Liquibase:
    • I’m using Liquibase to create my schema, migrate my database, and create my test data.
    • In liquibase I create my table like this:
      <createTable tableName="organization">
      			<column name="id" type="bigint" autoIncrement="true">
      				<constraints primaryKey="true" />
    • Notice autoIncrement=”true”
      • In HSQLDB this defaults to increment the max id by one.
      • In Postgres this defaults to using a sequence
  • Second Hibernate:
    • In Hibernate I’m setting my entity PK id to GenerationType.IDENTITY
      	private Long id;
    • Like with Liquibase this defaults to:
      • In HSQLDB this defaults to increment the max id by one.
      • In Postgres this defaults to using a sequence
  • Third and most importantly importing of Test Data
    • In itself this what Liquibase and Hibernate is doing is NOT a problem. The real issue is that we’re importing quite a bit of TEST DATAwith set id’s to manage the relationships between tables.
      • For this reason we essentially circumvent the Postgres sequence which remains at 1, and causes the problem.
      • (No problem in HSQLDB with just increments whatever the max id is)


  • Use negative numbers in test data. This is what we did and it worked like a charm and was easy.
  • Define a POSTGRESQL only changeset in Liquibase that sets the sequence number. We opted not to do this as I hate mucking up deployment more than is absolutely necessary.
  • Apparently you can also state a starting number when using autoIncrement in liquibase which may be another way around this.

It's only fair to share...
Share on Facebook
Tweet about this on Twitter
Share on LinkedIn

Leave a Reply