JPA and PostgreSQL with GenerationType.IDENTITY

Question

I have a question about Postgres and GenerationType.Identity vs Sequence

In this example...

@Id
@SequenceGenerator(name="mytable_id_seq",
                   sequenceName="mytable_id_seq",
                   allocationSize=1)
@GeneratedValue(strategy = GenerationType.SEQUENCE,
                generator="mytable_id_seq")

I understand that I am specifying a Postgres sequence to use via annotations.

However, I have an id column defined with the 'serial' type, I have read that I can simply use GenerationType.IDENTITY and it will automatically generate a db sequence and use it to auto increment.

If that's the case, I don't see an advantage to using the SEQUENCE annotations unless you are using an integer for an id or have some specific reason to use another sequence you have created. IDENTITY is alot less code and potentially makes it portable across databases.

Is there something I'm missing?

Thanks in advance for the feedback.

Prudy

One is generated on INSERT, and the other is not. One is not known in PrePersist, the other is known. – Oscillation

Answer 1

If you have a column of type SERIAL, it will be sufficient to annotate your id field with:

@Id @GeneratedValue(strategy=GenerationType.IDENTITY)

This is telling Hibernate that the database will be looking after the generation of the id column. How the database implements the auto-generation is vendor specific and can be considered "transparent" to Hibernate. Hibernate just needs to know that after the row is inserted, there will be an id value for that row that it can retrieve somehow.

If using GenerationType.SEQUENCE, you are telling Hibernate that the database is not automatically populating the id column. Instead, it is Hibernate's responsibility to get the next sequence value from the specified sequence and use that as the id value when inserting the row. So Hibernate is generating and inserting the id.

In the case of Postgres, it happens that defining a SERIAL column is implemented by creating a sequence and using it as a default column value. But it is the database that is populating the id field so using GenerationType.IDENTITY tells Hibernate that the database is handling id generation.

These references may help:

http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#identifiers-generators

https://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL

Banausic

Great. Thank you for confirming my understanding of this! – Prudy

@Prudy could you, please, mark it as a correct answer – Busywork

exactly what I wanted. I can save my weekend – Federicofedirko

Remember using GenerationType.IDENTITY is a lot more inefficient than GenerationType.SEQUENCE. Using IDENTITY will cause Hibernate to do a full INSERT and then a new SELECT and then a final INSERT. It will also disable bulk inserts. – Moffitt

Answer 2

From "Pro JPA2" book:

"Another difference, hinted at earlier, between using IDENTITY and other id generation strategies is that the identifier will not be accessible until after the insert has occurred. Although no guarantee is made about the accessibility of the identifier before the transaction has completed, it is at least possible for other types of generation to eagerly allocate the identifier. But when using identity, it is the action of inserting that causes the identifier to be generated. It would be impossible for the identifier to be available before the entity is inserted into the database, and because insertion of entities is most often deferred until commit time, the identifier would not be available until after the transaction has been committed."

Martinmas

Answer 3

I think it can be helpful if you are using the same sequence for more than one table (for example you want a unique identifier for many types of bills) ... also If you want to keep track of the sequence away from the auto generated key

Unbounded

Answer 4

You can find here the solution of updating the PostgreSQL table creation accordingly, in order to work with the GenerationType.IDENTITY option.

Semifinalist

Answer 5

When using GenerationType.IDENTITY with SERIAL in the postgres table the ids become non-consecutive. The ids will become 1,2,3,31,32,33. Here we have a problem and IDENTITY doesn't work with SERIAL properly.

Gilbertogilbertson