Hibernate6.4 Pgsql Enum problem

Hibernate ORM
1

greetings,here i got a problem in springboot 3.1.7 with data jpa,and hibernate6.2
i define a enum type named Member.UserRole in Postgresql 15
and class defined like

@Column(name = "role")
    @Enumerated(EnumType.STRING)
    private UserRole role;

@Getter
    public enum UserRole {
        SuperAdmin("Super Admin"),
        SystemAdmin("System Admin"),
        OrganizeLeader("Org Leader"),
        OrganizeUser("Normal User");

        private final String displayName;

        UserRole(String displayName) {
            this.displayName = displayName;
        }
    }

while select, it works fine.but while i save it,the error occured:

2024-01-13T11:49:25.716+08:00  WARN 25044 --- [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42804
2024-01-13T11:49:25.716+08:00 ERROR 25044 --- [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : 错误: 字段 "role" 的类型为 member."UserRole", 但表达式的类型为 character varying
  建议：你需要重写或转换表达式
  位置：102

seems it need a custom cast from Java String to Pgsql Enum.
please advice how can i do in this? thanks

2

Hey there, can you please switch the language on PostgreSQL to English? I do not understand that language and error message, so it’s hard to tell what the problem is.

3

sorry i have no idea what is original string.but seems it said that
field is a type of member.“UserRole” in postgresql,but it’s a varchar in entity define,
advice: you need rewrite or cast expression

4

Please try to create a reproducer with our test case template (hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) and if you are able to reproduce the issue, create a bug ticket in our issue tracker(https://hibernate.atlassian.net) and attach that reproducer.

5

I have a similar issue with updating an object. here is the details

@Enumerated(EnumType.STRING)
    @JdbcTypeCode(SqlTypes.NAMED_ENUM)
    @Column(table = "", name = "", columnDefinition = "my_enum")
    @JdbcType(PostgreSQLEnumJdbcType.class)
    private MyEnum myEnum;

Error log
20:09:38.805 |ERROR [https-jsse-nio-127.0.0.1-4443-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper - ERROR: type "myEnum" does not exist Position: 447 at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197) at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.performNonBatchedMutation(AbstractMutationExecutor.java:107) at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorSingleNonBatched.performNonBatchedOperations(MutationExecutorSingleNonBatched.java:40) at org.hibernate.engine.jdbc.mutation.internal.AbstractMutationExecutor.execute(AbstractMutationExecutor.java:52) at org.hibernate.persister.entity.mutation.UpdateCoordinatorStandard.doStaticUpdate(UpdateCoordinatorStandard.java:778) at org.hibernate.persister.entity.mutation.UpdateCoordinatorStandard.performUpdate(UpdateCoordinatorStandard.java:324) at org.hibernate.persister.entity.mutation.UpdateCoordinatorStandard.coordinateUpdate(UpdateCoordinatorStandard.java:242) at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:2810) at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:168) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:632) at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:499) at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:363) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:41) at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127) at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1403) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:484) at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2319) at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1976) at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl.beforeCompletionCallback(JdbcResourceLocalTransactionCoordinatorImpl.java:169) at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:267) at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101) at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:561) ... 211 common frames omitted Caused by: org.postgresql.util.PSQLException: ERROR: type "myEnum" does not exist Position: 447 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2712) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2400) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:367) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415) at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190) at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:194) ... 233 common frames omitted

Generated SQL
Position: 447] [merge into table as t using (select cast(? as integer) id, cast(? as boolean) flag, cast(? as text) created_by, cast(? as timestamp(6)) created_ts, cast(? as text) description, cast(? as MyEnum) my_enum) as s on (t.id=s.id) when not matched then insert (id, flag, created_by, created_ts, description, my_enum) values (s.id, s.flag, s.created_by, s.created_ts, s.description, s.my_enum ....

6

Please try to create a reproducer with our test case template (hibernate-test-case-templates/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java at main · hibernate/hibernate-test-case-templates · GitHub) and if you are able to reproduce the issue, create a bug ticket in our issue tracker(https://hibernate.atlassian.net) and attach that reproducer.