PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – postgres enum hibernate 6 takes wrong type name

Here is my setup: spring boot 3 + hibernate 6 + postgres 15

I have an entity with field that is enum:

@Slf4j
@Getter
@Setter
@Entity
@Table(name = "foo")
public class FooEntity  {


    @Column(name = "favorite_fruit_type", nullable = true, columnDefinition = "some_enum_type_name")
    @Enumerated(EnumType.STRING)
    @JdbcTypeCode(SqlTypes.NAMED_ENUM)
    @JdbcType(PostgreSQLEnumJdbcType.class)
    private FruitType fruitType;

}

//and enum

public enum FruitType {
 APPLE, ORANGE, GRAPES
}

and I use dedicated enum type in postgres

create type some_enum_type_name as enum ('APPLE', 'ORANGE', 'GRAPES');


create table foo
(
    favorite_fruit_type     some_enum_type_name  null,
...
)

All works fine when I save/update/read records with such field but when I try to make a query in repository:

    @Query("""
            select ... 
where 
               fruitType in (
                    some.package.enums.FruitType.APPLE,
                    some.package.enums.FruitType.ORANGE
                )
            """)
    List<FooEntity> findSomething();

I have sql exception as hibernate generated query uses wrong enum name in case operator.
Ignoring the name that I specified with

@Column(name = "favorite_fruit_type", nullable = true, columnDefinition = "some_enum_type_name")" )

and wrong generated sql is:

select .... where  in ('APPLE'::FruitType,'ORANGE'::FruitType)

as you can see sql query uses cast to type equal to java enum class name and not name that I explicitly specified in column definition.

Any ideas ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    well, I had my share of debugging hibernate code to find code that is responsible for generating sql and now can say for sure that absolutely ignores your column definition and supposes that enum name must match java class name:

    org.hibernate.dialect.PostgreSQLEnumJdbcType

        @Override
    public <T> JdbcLiteralFormatter<T> getJdbcLiteralFormatter(JavaType<T> javaType) {
        return (appender, value, dialect, wrapperOptions) -> appender.appendSql( "'" + ((Enum<?>) value).name() + "'::"
                + dialect.getEnumTypeDeclaration( (Class<? extends Enum<?>>) javaType.getJavaType() ) );
    }

    and method "dialect.getEnumTypeDeclaration" has no access to column definition and always returns java class name

    why it was implemented so poorly I dunno: this is clear no-go for inherited db and vary bad design practice when names in java and postgres must match


  2. 0

    I think you need to customize how Hibernate handles the enum type in queries to use the correct PostgreSQL enum type. There are a couple of ways to do that, but one that I have tried in the past that worked for me was using @Enumerated(EnumType.STRING). This ensures the enum is stored and retrieved as a string value in the database.

    https://www.baeldung.com/jpa-persisting-enums-in-jpa

    @Getter
@Setter
@Entity
@Table(name = "foo")
public class FooEntity {

    @Enumerated(EnumType.STRING) // Store enums as strings
    @Column(name = "favorite_fruit_type", nullable = true, columnDefinition = "some_enum_type_name") // Use the correct PostgreSQL enum type
    private FruitType fruitType;

}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search