Postgres PostgresqlBadGrammarException Fix

Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: column "status" is of type user_status_enum but expression is of type character varying

I Encountered This Error When developing my E_COMMERCE BACKEND

It typically means the that r2dbc is trying to insert a string type to a field the if defined as Enum in your postgres database.

To Fix the issue Follow these steps

Configure the ConnectionFactory to convert string values to the Enum type

package org.abraham.user_service.config;



import io.r2dbc.postgresql.PostgresqlConnectionConfiguration;
import io.r2dbc.postgresql.PostgresqlConnectionFactory;
import io.r2dbc.postgresql.codec.EnumCodec;
import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactories;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryOptions;
import lombok.AllArgsConstructor;
import org.abraham.user_service.dto.AddressType;
import org.abraham.user_service.dto.UserRoles;
import org.abraham.user_service.dto.UserStatus;
import org.reactivestreams.Publisher;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.r2dbc.config.AbstractR2dbcConfiguration;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static io.r2dbc.spi.ConnectionFactoryOptions.*;



@Configuration
@AllArgsConstructor
public class R2dbcConfig extends AbstractR2dbcConfiguration {

    private R2dbcProperties r2dbcProperties;

    @Override
    @Bean
    public ConnectionFactory connectionFactory() {
        Map<String, String> options = new HashMap<>();
        options.put("lock_timeout", "10s");

        return new PostgresqlConnectionFactory(PostgresqlConnectionConfiguration.builder()
                .host("localhost")
                .port(5434)  // optional, defaults to 5432
                .username(r2dbcProperties.getUsername())
                .password(r2dbcProperties.getPassword())
                .database("user_service_db")  // optional
                .options(options) // optional
                .codecRegistrar(EnumCodec.builder().withEnum("user_status_enum", UserStatus.class).build())
                .codecRegistrar(EnumCodec.builder().withEnum("address_type_enum", AddressType.class).build())
                .codecRegistrar(EnumCodec.builder().withEnum("user_roles", UserRoles.class).build())
                .build());
    }

    @Override
    protected List<Object> getCustomConverters() {
        return List.of(
                new EnumConverters.UserStatusToStringConverter(),
                new EnumConverters.StringToUserStatusConverter()
        );
    }

}

Upto this point Everything should woul perfectly. From this explanation

Postgres Enum Types

Applications may make use of Postgres enumerated types by using EnumCodec to map custom types to Java enum types. EnumCodec requires the Postgres OID and the Java to map enum values to the Postgres protocol and to materialize Enum instances from Postgres results. You can configure a CodecRegistrar through EnumCodec.builder() for one or more enumeration type mappings. Make sure to use different Java enum types otherwise the driver is not able to distinguish between Postgres OIDs.

Example:

SQL:

CREATE TYPE my_enum AS ENUM ('FIRST', 'SECOND');

Java Model:

enum MyEnumType {
  FIRST, SECOND;
}

Codec Registration:

PostgresqlConnectionConfiguration.builder()
        .codecRegistrar(EnumCodec.builder().withEnum("my_enum",MyEnumType.class).build());

When available, the driver registers also an array variant of the codec.

here is the github link for more explanation: https://github.com/pgjdbc/r2dbc-postgresql