Configure JPA to let PostgreSQL generate the primary key value

by

Given the table definition:

CREATE TABLE webuser(
    idwebuser SERIAL PRIMARY KEY,
    ...
)

Use the mapping:

@Entity
@Table(name="webuser")
class Webuser {

    @Id
    @SequenceGenerator(name="webuser_idwebuser_seq",
                       sequenceName="webuser_idwebuser_seq",
                       allocationSize=1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE,
                    generator="webuser_idwebuser_seq")
    @Column(name = "idwebuser", updatable=false)
    private Integer id;

    // ....

}

The naming tablename_columname_seq is the PostgreSQL default sequence naming for SERIAL and I recommend that you stick to it.

The allocationSize=1 is important if you need Hibernate to co-operate with other clients to the database.

Note that this sequence will have “gaps” in it if transactions roll back. Transactions can roll back for all sorts of reasons. Your application should be designed to cope with this.

  • Never assume that for any id n there is an id n-1 or n+1
  • Never assume that the id n was added or committed before an id less than n or after an id greater than n. If you’re really careful with how you use sequences you can do this, but you should never try; record a timestamp in your table instead.
  • Never add to or subtract from an ID. Compare them for equality and nothing else.

See the PostgreSQL documentation for sequences and the serial data types.

They explain that the table definition above is basically a shortcut for:

CREATE SEQUENCE idwebuser_id_seq;
CREATE TABLE webuser(
    idwebuser integer primary key default nextval('idwebuser_id_seq'),
    ...
)
ALTER SEQUENCE idwebuser_id_seq OWNED BY webuser.idwebuser;

… which should help explain why we have added a @SequenceGenerator annotation to describe the sequence.

If you really must have a gap-less sequence (for example, cheque or invoice numbering) see gapless sequences but seriously, avoid this design, and never use it for a primary key.

Note: If your table definition looks like this instead:

CREATE TABLE webuser(
    idwebuser integer primary key,
    ...
)

and you’re inserting into it using the (unsafe, do not use):

INSERT INTO webuser(idwebuser, ...) VALUES ( 
    (SELECT max(idwebuser) FROM webuser)+1, ...
);

or (unsafe, never do this):

INSERT INTO webuser(idwebuser, ...) VALUES ( 
    (SELECT count(idwebuser) FROM webuser), ...
);

then you’re doing it wrong and should switch to a sequence (as shown above) or to a correct gapless sequence implementation using a locked counter table (again, see above and see “gapless sequence postgresql” in Google). Both the above do the wrong thing if there’s ever more than one connection working on the database.

Leave a Comment

tech