Search code examples
spring-dataspring-data-r2dbcr2dbc-postgresql

spring R2DBC query by enum type

My PostgreSQL contains an enum like 

create type my_type as enum('VAL1', 'VAL2')

in Spring boot app, it is represented by MyType.class enum

I'm trying to run a simple query using DatabasClient

client.exectute("select * from table where type = :type")...

As an error I'm getting:

ceptionFactory$PostgresqlBadGrammarException: operator does not exist: type = character varying

Casting type to my_type doesn't work (both with CAST and ::)

I've already registered a specific codec for MyType.class which works - querying all without conditions works with the relevant @ReadingConverter

Solution

  • Neither Spring Data R2DBC nor R2DBC Postgres can map your enum type to Postgres enum types.

    You can still use Postgres enum types represented as strings if you properly cast the bind value/column value on retrieval in your SQL statement.

    Example:

    client.exectute("select type::text from table where type = :type::my_type")