Why is GenerationType.AUTO not using a serial on PostgreSQL?

Question

I have an application using JPA (with eclipselink). The application was developed with a Derby database in the background. The tables were generated by JPA itself. This is a simple example of one of the entities:

public class MyEntity implements Serializable {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Long id;

  @Column(nullable=false)
  private String someString;
}

JPA then created another table SEQUENCE to generate the ID. We now want to switch to PostgreSQL and instead of a table SEQUENCE we want to use real sequences in PostgreSQL. This is the DDL we used to create the table:

CREATE TABLE MYENTITY (
  ID serial PRIMARY KEY NOT NULL,
  SOMESTRING varchar(255) NOT NULL
)

But still JPA wants to use the SEQUENCE table (that doesn't exist):

Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.          persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: relation "sequence" does not exist
    Position: 8
Error Code: 0
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
    bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQUENCE" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ? ")

I know that I could change the generation strategy to SEQUENCE, but I want to have the code portable. GenerationType.SEQUENCE shouldn't work with databases that don't support sequences. And I expect that AUTO would use a sequence on PostgreSQL. Why doesn't it? Do I have to do something else?

As a note, it seems that JPA used a sequence automatically in the question Generated Value in Postgres.

Neil Stockton · Accepted Answer · 2015-02-08 11:39:10Z

9

If you want to have the id assigned "in datastore" (i.e via auto-generate, or SERIAL) you should use IDENTITY.

Similarly, if you want to use a datastore SEQUENCE then select SEQUENCE.

AUTO means leave it to the JPA implementation to decide, and it may decide something else to what you hoped.

edited Feb 8, 2015 at 11:39

answered Feb 8, 2015 at 11:22

Neil Stockton

11.6k3 gold badges36 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why is GenerationType.AUTO not using a serial on PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related