Can I use sequences with Postgres?

Yes you can. However, there is a "but" involved.

Sequences within Postgres have been through a roller coaster ride during the development of V2, and there's a lot of older information about how they're used within Glassfish and the JPA. It's my hope that this page can be an authoritative resource for the subject.

The simplest mechanism for using sequences is to use the GenerationType.IDENTITY strategy for a GeneratedValue annotation:

@Id
    @Column(name = "id", nullable = false)
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;

In order for this to work, the column type in the associated table must be SERIAL. In this instance, you are transparently using the SEQUENCE that is automatically created by the Postgres database when a SERIAL field is defined. The other benefit is that if you use the facility within Glassfish to create tables on deployment, then Glassfish creates the proper SERIAL column for you in the generated table.

The JPA provides for a more specific use of sequences through the GenerationType.SEQUENCE on the GeneratedValue annotation.

Unfortunately, this is not supported directly by Glassfish JPA implementation, Toplink, for the Postgres.

There is an internal architectural limitation within Toplink that prevents a specific database platform from supporting both the IDENTITY type of generator, and a SEQUENCE type of generator simultaneously.

The Toplink implementors decided to support the Postgres SERIAL column using the IDENTITY type for the standard Postgres DB Platform implementation, thus disabling the SEQUENCE type of generator.

However, all is not lost. Toplink allows you to specify which DB platform your application wishes to use, and the default implementations of the DB platforms are readily extensible.

Using this mechanism, we can create a new Postgres platform that supports SEQUENCE much like Oracle does, rather than supporting the SERIAL column and IDENTITY.

In order to do this, a new class must be created.

// Code free for any use whatsoever.
package faq.glassfish.postgres;

import oracle.toplink.essentials.queryframework.ValueReadQuery;
import oracle.toplink.essentials.sessions.DatabaseSession;

public class PostgresPlatform 
    extends oracle.toplink.essentials.platform.database.PostgreSQLPlatform {
    
    public PostgresPlatform() {
        super();
    }
        
    public boolean shouldNativeSequenceAcquireValueAfterInsert() {
        return false;
    }
    
    public ValueReadQuery buildSelectQueryForNativeSequence(String seqName, Integer size) {
        ValueReadQuery selectQuery = new ValueReadQuery();
        selectQuery.setSQLString("select nextval(\'"  + seqName + "\')");
        return selectQuery;
    }
    
    public void platformSpecificSequencingInitialization(DatabaseSession session) {
    }
}

To build this code, you will need to add the toplink jar from the Glassfish distribution to your build classpath.

It's located in $glassfish_install_dir/lib/toplink-essentials.jar

This code changes the default Postgres DB Platform to switch over to a sequence behavior like Oracles.

Simply add that class to your project, and then add a property to your persistence.xml file:

<property name="toplink.platform.class.name" 
          value="faq.glassfish.postgres.PostgresPlatform"/>

Now, to use the sequence in your entities:

@Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="MYSEQUENCE")
    @SequenceGenerator(name="MYSEQUENCE", sequenceName="mysequence")
    @Column(name = "id", nullable = false)
    private Integer id;

As an added bonus, you can leverage the allocationSize parameter of the sequence as well. You simply need to change the increment value of your Postgres sequence to match the allocation size.

So, if you add the allocationSize=10 to the SequenceGenerator annotation, then in your database, you would simply need to:

ALTER SEQUENCE mysequence INCREMENT 10;