Re: [JDBC] JPA + enum == Exception

> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Marc G. Fournier
> I'm trying to use enum's in a database, but the java guys are telling me that they are having problems with inserts ...
> reading from the database isn't a problem, but there appears to be an issue with converting from string -> enum when saving it back again ...

This is interesting, it seems to be a difference between executing the
sql directly and using a prepared statement:

tomtest=# create type mood as enum ('happy', 'meh', 'sad');
CREATE TYPE
tomtest=# create table enumcast (current_mood mood);
CREATE TABLE
tomtest=# insert into enumcast values ('sad');
INSERT 0 1
tomtest=# select * from enumcast ;
current_mood
--------------
sad
(1 row)

That works ok, but when attempting to use a prepared statement:

ps = con.prepareStatement("insert into enumcast values (?)");
ps.setString(1, "meh");
ps.executeUpdate();

we get a

org.postgresql.util.PSQLException: ERROR: column "current_mood" is of
type mood but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

Cue sad trombone. You can fix this with implicit casts using CREATE
CAST, or an explicit cast in the query, but this shouldn't really be
necessary for what is a basic use case for enums. In any case ORMs
won't know how to do that without writing custom converters, which
makes me sad. I had intended that ORMs could just treat enum fields as
text fields basically and not have to care about the underlying
implementation.

Cc'ing hackers - why the difference here? I presume that the input
function is getting triggered when the value is inline in the SQL, but
not so when the statement is prepared. Should we consider creating an
implicit cast from text to enums when we create an enum? Or is there
some other way to get the expected behaviour here?

Cheers

Tom