[FIXED] Generate primary key with @GeneratedValue in JPA as Sequence, but make it work using external Database Access

Issue

I am using PostgreSQL 12.11, JPA 3.1.0, and Hibernate 5.6.10. I do not want to overspecify the problem, but some aspects might depend on the implementation.

I'd like to generate a table with an automatically generated BIGINT id value as primary key, and the table should be automatically generated from JPA, but it should also be accessible with external tools.

1. If I use @GeneratedValue in JPA (even with GenerationType.SEQUENCE), the default value column annotation of the database will not be set, so the PostgreSQL-specific ID generation will not be not working.

2. If I use @GeneratedValue and columnDefinition = "BIGSERIAL" in JPA, there will always be two sequences, even if specify the appropriate name, and I am pretty sure that this will create conflicting values. (I have not tested it, though.)

3. If I do not use @GeneratedValue at all, but columnDefinition = "BIGSERIAL", I will be able to insert with other tools, but not with JPA, where the id will always be 0.

4. If I do use @GeneratedValue and set the default value to the nextval call of the sequence manually using columnDefinition, it will fail at generation because the sequence does not exist at that time.

What I am doing now is using variant 1. For external access, I manually set the id to the result of nextval. This is not perfect. For COPY, I even need a TEMPORARY TABLE. Is there some "optimal way" this should be done?

Solution

Thanks to @MWiesner, I could solve this (compare comment above):

As it seems, my problem was part of a larger compatibility issue with PostgreSQL before version 10+. Since then, it is possible (and recommended) to use GenerationType.IDENTITY.

By researching this, I also learned that PostgreSQL 10+ IDENTITY uses as sequence in the background - this is important, as many older websites discourage IDENTITY because of performance issues. AFAICS this is not true for this newer implementation.

So what I am doing now is using GenerationType.IDENTITY and everything works fine.

Some more trivia:

• For my versions mentioned above, GenerationType.AUTO defaults to SEQUENCE, not IDENTITY.
• JPA sets the PostgreSQL configuration "generated by default as identity" not "generated always as identity". Depending on the situation, this might be inferior. I have not found out how to change that yet.

Answered By - Tim Lammarsch
Answer Checked By - Mary Flores (JavaFixing Volunteer)