Incompatible sequence names between Liquibase and Hibernate JPA for PostgreSQL and H2

Question

I have an app running with standard Hibernate JPA and Liquibase for generating the db. I use H2 for testing and PostgreSQL when running.

My problem is I can't seem to get this setup to work nicely for primary keys with sequence generation.

When I have an entity id like this:

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

and use liquibase to create the database, like this:

<column name="id" type="BIGINT" autoIncrement="true" incrementBy="1">
    <constraints nullable="false" primaryKey="true" />
</column>

it works fine in H2, but for PostgreSQL Hibernate complains that it is:

"Missing sequence or table: hibernate_sequence"

I can fix this for PostgreSQL by changing the JPA @GeneratedValue to something like:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "text_id_seq")
@SequenceGenerator(name = "text_id_seq", sequenceName = "text_id_seq", allocationSize = 1)
private Long id;

But now the H2 sequence won't match what Hibernate expects.

There doesn't seem to be any easy way to make sure Liquibase the sequence with a specific name. What can I do to get this setup to work?

I'm looks like I'm currently running

liquibase.version 2.0.4
hibernate 4.1.7
postgres driver 9.1-901.jdbc3
postgres 9.2.1 (at least locally)
h2 1.3.168

What about GenerationType.IDENTITY ? In general, IMO, this is one of the warts of JPA; all this configuration-by-annotations makes it hard to adjust something when it needs to vary by target database. — Craig Ringer
Thanks @CraigRinger, a bit embarrasing that I didn't think to try that. Works like a charm across H2, pg and mysql. It would be better if AUTO worked across liquibase and JPA, but not that big a deal. — storset

Craig Ringer Craig Ringer · Accepted Answer · 2012-10-31T11:29:30

Use GenerationType.IDENTITY. This should work on most DBs.

Identity generation is one of the warts of JPA in my opinion; it's too hard to set global overrides for generation options based on the DB you're using, etc. Doing it in annotations makes it hard to adjust programmatically.

Hibernate's bizarre hibernate_sequence is particularly painful. It's beyond me why it doesn't use the PostgreSQL default sequences for generated columns.

Incompatible sequence names between Liquibase and Hibernate JPA for PostgreSQL and H2

1 Answers