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.

Answer 1

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.