Inserting into custom SQL types with prepared statements in java

Question

I have some custom types. They are all basically enums. Here is an example of what they look like:

CREATE TYPE card_suit AS ENUM
   ('spades',
    'clubs',
    'hearts',
    'diamonds');

And I have some prepared statements in Java, which look something like this:

// Setup stuff up here.
sql = "INSERT INTO foo (suit) VALUES (?)";
st.setString(1, 'spades');
st.executeUpdate(sql);

And Java gives me some nasty exceptions like this:

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

It's nice of them to give me a hint, but I'm not sure exactly how to follow it.

If you have control of the schema, a check constraint on a text column can work the same as the enum, yet is easier to work with in Java. — BillRobertson42
– BillRobertson42, Commented Feb 26, 2016 at 21:13

dani herrera · Accepted Answer · 2012-05-13 12:54:26Z

27

Have you tried to cast column to enum?

// Setup stuff up here.
sql = "INSERT INTO foo (suit) VALUES (?::card_suit)";
st.setString(1, 'spades');
st.executeUpdate(sql);

Explained in Convert between Java enums and PostgreSQL enums article of 'A web coding blog' with samples:

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, ?);

answered May 13, 2012 at 12:54

dani herrera

52k11 gold badges133 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Carrick Over a year ago

Exactly what I needed. Haven't used casting in SQL before. Thanks!

abbas · Accepted Answer · 2020-03-17 15:56:07Z

0

Another approach would have been

st.setObject(1, 'spades', Types.OTHER);

answered Mar 17, 2020 at 15:56

abbas

7,2194 gold badges43 silver badges39 bronze badges

Collectives™ on Stack Overflow

Inserting into custom SQL types with prepared statements in java

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related