Allow StringCodec registration for CITEXT usage

[tobq]

Bug Report

select 'text'::citext = :param fails when :param is set to (String) "TEST".

This seems to be an issue with the driver explicitly setting the type of the string parameter - as noted here: https://stackoverflow.com/a/23721681/7759514

For hibernate, this can be fixed using the properties: spring.datasource.hikari.data-source-properties.stringtype=unspecified / spring.datasource.tomcat.connection-properties.stringtype=unspecified
However these properties seem to have no effect with r2dbc

Versions

• Driver: 0.8.10.RELEASE
• Database: postgres 14
• Java: 17

[mp911de]

Leaving https://github.com/pgjdbc/pgjdbc/blob/5c78edb1412467636f996416ad37118f651db86e/pgjdbc/src/main/java/org/postgresql/jdbc/PgPreparedStatement.java#L253-L255 here as reference.

Can you upgrade to at least 0.9.2.RELEASE and bind the parameter value using Parameters.in(PostgresqlObjectId.UNSPECIFIED, "…")? Alternatively, rewrite the SQL to apply parameter casting to ::citext.

It would make sense to use the same binding customization mechanism as in pgjdbc.

[tobq]

I had already tried select 'text'::citext = :param::citext - I'll try Parameters.in(PostgresqlObjectId.UNSPECIFIED, "…")

[tobq]

@mp911de 0.9.2 doesn't seem to exist - the latest on maven is 0.8.13
https://search.maven.org/search?q=a:r2dbc-postgresql

[mp911de]

We switched to a different group Id, org.postgresql, see https://search.maven.org/artifact/org.postgresql/r2dbc-postgresql

[tobq]

@mp911de for query.bind("param", Parameters.in(PostgresqlObjectId.UNSPECIFIED, param)) I get Cannot encode parameter of type io.r2dbc.spi.Parameters$InParameter

[mp911de]

I'm not able to reproduce the issue that you've reported.

Here's my reproducer code that shows what currently works and what not:

SERVER.getJdbcOperations().execute("CREATE EXTENSION IF NOT EXISTS citext WITH SCHEMA public");

SERVER.getJdbcOperations().execute("DROP TABLE IF EXISTS test");
SERVER.getJdbcOperations().execute("CREATE TABLE test ( ci CITEXT, cs VARCHAR )");

this.connection.createStatement("INSERT INTO test VALUES($1, $1)").bind("$1", "HeLlO")
    .execute()
    .flatMap(PostgresqlResult::getRowsUpdated)
    .as(StepVerifier::create)
    .expectNext(1)
    .verifyComplete();

this.connection.createStatement("SELECT ci FROM test WHERE ci = $1::citext")
    .bind("$1", "Hello")
    .execute()
    .flatMap(it -> it.map(r -> r.get("ci")))
    .as(StepVerifier::create)
    .expectNext("HeLlO")
    .verifyComplete();

this.connection.createStatement("SELECT ci FROM test WHERE ci = $1")
    .bind("$1", Parameters.in(PostgresqlObjectId.UNSPECIFIED, "Hello"))
    .execute()
    .flatMap(it -> it.map(r -> r.get("ci")))
    .as(StepVerifier::create)
    .expectNext("HeLlO")
    .verifyComplete();

// CITEXT comparison against UNSPECIFIED works
this.connection.createStatement("SELECT cs::citext = $1 FROM test")
    .bind("$1", Parameters.in(PostgresqlObjectId.UNSPECIFIED, "Hello"))
    .execute()
    .flatMap(it -> it.map(r -> r.get(0)))
    .as(StepVerifier::create)
    .expectNext(true)
    .verifyComplete();

// CITEXT != VARCHAR
this.connection.createStatement("SELECT cs::citext = $1 FROM test")
    .bind("$1", "Hello")
    .execute()
    .flatMap(it -> it.map(r -> r.get(0)))
    .as(StepVerifier::create)
    .expectNext(false)
    .verifyComplete();