java enum and postgresql enum

Question

CREATE TABLE customers
(
  first_name character varying(15),
  second_name character varying(20),
  login character varying(15) NOT NULL,
  password character varying(15),
  email character varying(40),
  gender gender,
  register_date date,
  date_of_birth date,
  address character varying(40),
  address_number integer,
  town character varying(20),
  CONSTRAINT login PRIMARY KEY (login)
)

I have this table and I created an enum of gender such as:

CREATE TYPE gender AS ENUM ( 'F', 'M',);

I am trying to insert into customers data from eclipse java with PreparedStatement but there is an error such as ERROR: column "gender" is of type gender but expression is of type character varying Hint: You will need to rewrite or cast the expression.

My Java code looks like:

PreparedStatement pre_state;

public enum gendertype {
    F,
    M;
}

pre_state = conn.prepareStatement("INSERT INTO"
            + " customers VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
pre_state.set(6, gendertype.F.toString());

It's probably inserting it as 'F'::text, which would cause the error. — Mike Christensen
– Mike Christensen, Commented May 10, 2013 at 23:59
pre_state=main.conne.conn.prepareStatement("insert into customers values (?,?,?,?,?,?::gender,?,?,?,?,?)"); — user2317288
– user2317288, Commented May 11, 2013 at 0:14
Please consider adding an Other and/or unspecified value for gender. Gender isn't simply two values; you have genetically XXY individuals, developmentally intersex individuals, etc. Like assuming that names has a "first name" and "surname" it's a bad habit in computing that needs to go away. — Craig Ringer
– Craig Ringer, Commented May 11, 2013 at 0:40

Drew Noakes · Accepted Answer · 2014-02-13 10:48:37Z

15

I can take no credit for this answer as you have already solved it, but I will explain why it works.

PostgreSQL provides the answer when it says

Hint: You will need to rewrite or cast the expression

The Java code is creating a string literal value that represents the Java enum gendertype type.

Casting a literal to a PostgreSQL gender type is done by adding a casting suffix to the value ::gender.

So valid input would be

'F'::gender

or

'M'::gender

This works because all PostgreSQL types have a input method that takes a text representation and converts that to the internal form.

edited Feb 13, 2014 at 10:48

Drew Noakes

313k174 gold badges704 silver badges770 bronze badges

answered May 11, 2013 at 11:49

Tim Child

3,0221 gold badge28 silver badges25 bronze badges

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

Comments

abbas · Accepted Answer · 2020-03-17 15:57:22Z

2

Your solution would have been

pre_state.setObject(6, gendertype.F.toString(), Types.OTHER);

answered Mar 17, 2020 at 15:57

abbas

7,2194 gold badges43 silver badges39 bronze badges

Comments

Dmitriy Fialkovskiy · Accepted Answer · 2022-10-05 08:43:31Z

1

In addition to other answers if you use for example createNativeQuery() you should cast value to your enum like cast(? as gender), something like that:

Person person = new Person("Gurbanguly", "Berdymukhamedov", Gender.Male)
Session session = HibernateUtil.getSessionFactory().openSession();
Transaction transaction = session.beginTransaction();
session.createNativeQuery("INSERT INTO Person (firstname, lastname, gender) VALUES (?,?,cast(? as gender))")
            .setParameter(1, person.getFirstName())
            .setParameter(2, person.getLastName())
            .setParameter(3, person.getGender().toString())
            .executeUpdate();
transaction.commit();
session.close(); // this statement is needed not to produce many sessions

edited Oct 5, 2022 at 8:43

answered Oct 5, 2022 at 8:10

Dmitriy Fialkovskiy

3,2558 gold badges36 silver badges49 bronze badges

Comments

Steven Kuypers · Accepted Answer · 2024-07-04 13:30:42Z

1

In Postgres you can add a cast to the existing ones (in a database view, see your_database >> Database Objects >> casts).

Where you have already done something like

CREATE TYPE my_type AS enum ('SOME', 'OTHER');

you should also do

CREATE CAST (character varying AS my_type) with inout as assignment;

Under 'casts' (after a refresh), you will find the

varchar as my_type

when you scroll down the long list.

This should solve the problem without further code adjustments.

edited Jul 4, 2024 at 13:30

answered Jul 4, 2024 at 12:28

Steven Kuypers

4814 silver badges6 bronze badges

1 Comment

John Little Jan 9 at 13:57

This solved it for us, and avoided complex code changes in spring boot.

Collectives™ on Stack Overflow

java enum and postgresql enum

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related