Skip to content

Posts tagged ‘sql postgresql enum’

15
May

Convert between Java enums and PostgreSQL enums

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

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

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

1
2
3
4
5
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:

1
2
3
4
5
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:

1
2
3
4
5
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:

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

Retrieving the enum from a SELECT statement looks like this:

1
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:

1
2
SELECT enumlabel FROM pg_enum
    WHERE enumtypid = 'your_enum'::regtype ORDER BY oid;