Skip to content

May 15, 2011

1

Convert between Java enums and PostgreSQL enums

PostgreSQL allows you to create enum types using the following syntax:

CREATE TYPE animal_type AS ENUM('DOG', 'CAT', 'SQUIRREL');

You can now use ‘animal’ as a datatype in your tables, for example:

create table pet (                         
                  pet_id        integer         not null,
                  pet_type      animal_type     not null,
                  name          varchar(20)     not null
                  );

In Java, you’d have a corresponding enum type:

public enum AnimalType {
    DOG,
    CAT,
    SQUIRREL;
 }

Converting between Java and PostgreSQL enums is straightforward. For example, to insert or update an enum field you could use the CAST syntax in your SQL PreparedStatement:

INSERT INTO pet (pet_id, pet_type, name) VALUES (?, CAST(? AS animal_type), ?);

--or

INSERT INTO pet (pet_id, pet_type, name) VALUES (?, ?::animal_type, ?);

Postgres will also let you insert/update an enum just by passing its value as a string.

Whether casting or not, the Java side is the same. You would set the fields like this:

stmt.setInt(1, 1);
stmt.setString(2, AnimalType.DOG.toString());
stmt.setString(3, 'Rex');

Retrieving the enum from a SELECT statement looks like this:

AnimalType.valueOf(stmt.getString("pet_type"));

Take into consideration that enums are case-sensitive, so any case mismatches between your Postgres enums and Java enums will have to be accounted for. Also note that the PostgreSQL enum type is non-standard SQL, and thus not portable.

Also, FYI, to view the set of values in a given Postgres enum type, you can use the following SQL query:

SELECT enumlabel FROM pg_enum 
    WHERE enumtypid = 'your_enum'::regtype ORDER BY oid;
Read more from Java
1 Comment Post a comment
  1. Rob Mitchell
    Jan 24 2013

    While this looks great for regular Java, I’m using JPA and was relying upon EntityManager simply persisting my entities but it throws exception:

    @Entity
    @Table(name=”myentity”)
    public class MyEntity {

    @Id
    @SequenceGenerator(name = “entitySequence”, sequenceName = “myentity_seq”, allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = “entitySequence”)
    private int id;

    private int age;

    @Enumerated(EnumType.STRING)
    @Column(name=”gender_type”, columnDefinition=”GENDER_ENUM”)
    private GenderType gender;

    … /* getters and setters */
    }

    **********************************
    @Test
    public void test1_Create() {
    MyEntity myentity = new MyEntity();
    myentity.setAge(20);
    myentity.setGender(GenderType.Male);

    try {
    EntityManager em = JpaUtils.create();
    em.getTransaction().begin();
    em.persist(myentity);
    em.getTransaction().commit();


    }

    **********************************
    Caused by: org.postgresql.util.PSQLException: ERROR: column “gender_type” is of type gender_enum but expression is of type character varying
    Hint: You will need to rewrite or cast the expression.
    Position: 57
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)

    **********************************

    JPA v2.0
    PG v9.1
    PG JDBC 9.1-902
    Java 6
    Eclipse Juno

    Any ideas?

    Thx
    -Rob

    Reply

Share your thoughts, post a comment.

(required)
(required)

Note: HTML is allowed. Your email address will never be published.

Subscribe to comments