R2dbcEntityTemplate with UUID type errors out with "operator does not exist: uuid = character varying" for Postgres

[ozooxo]

R2dbcEntityTemplate update with UUID condition errors out with "operator does not exist: uuid = character varying" for Postgres.

template.update(Demo::class.java)
  .matching(Query.query(Criteria.where("pk").`is`(pk))) // where pk is type uuid in Postgres
  ...

errors out with

operator does not exist: uuid = character varying

It looks for me that R2dbcEntityTemplate explicitly casts java.lang.String to Postgres varchar. Is there a way to override it or choose a different type?

How to reproduce it?

Spin up a Postgres locally

docker run -e POSTGRES_HOST_AUTH_METHOD=trust -p 5432:5432 postgres

Login to that database, and create a table with an uuid primary key. And add some seed data.

$ psql -h localhost -p 5432 -U postgres -d postgres

postgres=# CREATE TABLE demo (
  pk uuid NOT NULL PRIMARY KEY,
  data text
);
CREATE TABLE

postgres=# INSERT INTO demo (pk, data) VALUES (
  'e90469b6-efe3-4e12-a07d-25bba7f27a05', 'foobar'
);
INSERT 0 1

Then try to UPDATE the demo table.

import io.r2dbc.postgresql.PostgresqlConnectionFactoryProvider
import io.r2dbc.spi.ConnectionFactoryOptions
import kotlinx.coroutines.reactor.awaitSingle
import kotlinx.coroutines.runBlocking
import org.junit.jupiter.api.Test
import org.springframework.data.annotation.Id
import org.springframework.data.r2dbc.core.DefaultReactiveDataAccessStrategy
import org.springframework.data.r2dbc.core.R2dbcEntityTemplate
import org.springframework.data.r2dbc.dialect.PostgresDialect
import org.springframework.data.relational.core.mapping.Column
import org.springframework.data.relational.core.mapping.Table
import org.springframework.data.relational.core.query.Criteria
import org.springframework.data.relational.core.query.Query
import org.springframework.data.relational.core.query.Update
import org.springframework.data.relational.core.sql.Expressions
import org.springframework.data.relational.core.sql.SQL
import org.springframework.data.relational.core.sql.SqlIdentifier
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.datasource.DriverManagerDataSource
import org.springframework.r2dbc.core.DatabaseClient
import java.util.UUID

@Table("demo")
data class Demo(
    @Id val pk: String,
    @Column("data") val data: String? = null,
)

class DemoTest {

    @Test
    fun testR2DBC(): Unit = runBlocking {
        val factory = PostgresqlConnectionFactoryProvider().create(
            ConnectionFactoryOptions.builder()
                .option(ConnectionFactoryOptions.HOST, "localhost")
                .option(ConnectionFactoryOptions.PORT, 5432)
                .option(ConnectionFactoryOptions.DATABASE, "postgres")
                .option(ConnectionFactoryOptions.USER, "postgres")
                .build()
        )
        val client = DatabaseClient.builder()
            .connectionFactory(factory)
            .bindMarkers(PostgresDialect.INSTANCE.bindMarkersFactory)
            .build()
        val strategy = DefaultReactiveDataAccessStrategy(PostgresDialect.INSTANCE)
        val template = R2dbcEntityTemplate(client, strategy)

        val pk = "e90469b6-efe3-4e12-a07d-25bba7f27a05"
        template.update(Demo::class.java)
            .matching(Query.query(Criteria.where("pk").`is`(pk)))
            .apply(
                Update.from(
                    mapOf(
                        SqlIdentifier.unquoted("data") to "bazqux",
                    )
                )
            ).awaitSingle()
    }
}

It errors out with

execute; bad SQL grammar [UPDATE demo SET data = $1 WHERE demo.pk = $2]
org.springframework.r2dbc.BadSqlGrammarException: execute; bad SQL grammar [UPDATE demo SET data = $1 WHERE demo.pk = $2]
...
Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42883] operator does not exist: uuid = character varying

I also tried explicit casting, but unfortunately I still get the same error.

.matching(
    Query.query(
        Criteria.where("pk").`is`(Expressions.cast(SQL.literalOf(pk), "uuid"))))

It is also the same error if I pass in a UUID instead of a string object.

.matching(
    Query.query(
        Criteria.where("pk").`is`(UUID.fromString(pk))))

Notice that Postgres is actually pretty flexible. Even if I don't explicitly casting pk = uuid('e90469b6-efe3-4e12-a07d-25bba7f27a05'), just using a text it works.

postgres=# UPDATE demo SET data = 'bazqux' WHERE pk = 'e90469b6-efe3-4e12-a07d-25bba7f27a05';
UPDATE 1

To trigger the same error, I need to explicitly cast the string to varchar type.

postgres=# UPDATE demo SET data = 'bazqux' WHERE pk = 'e90469b6-efe3-4e12-a07d-25bba7f27a05'::varchar;
ERROR:  operator does not exist: uuid = character varying

postgres=# UPDATE demo SET data = 'bazqux' WHERE pk = CAST('e90469b6-efe3-4e12-a07d-25bba7f27a05' AS varchar);
ERROR:  operator does not exist: uuid = character varying

Update a UUID type field doesn't work either

Create another table with primary key as text, but data is UUID type.

postgres=# CREATE TABLE demo2 (
  pk text NOT NULL PRIMARY KEY,
  data uuid
);
CREATE TABLE

postgres=# INSERT INTO demo2 (pk, data) VALUES (
  'foobar', 'e90469b6-efe3-4e12-a07d-25bba7f27a05'
);
INSERT 0 1

Then

@Table("demo2")
data class Demo2(
    @Id val pk: String,
    @Column("data") val data: String? = null,
)

...
        val value = "e90469b6-efe3-4e12-a07d-25bba7f27a05"
        template.update(Demo2::class.java)
            .matching(Query.query(Criteria.where("pk").`is`("foobar")))
            .apply(
                Update.from(
                    mapOf(
                        SqlIdentifier.unquoted("data") to value,
                    )
                )
            ).awaitSingle()

errors out with

execute; bad SQL grammar [UPDATE demo2 SET data = $1 WHERE demo2.pk = $2]
org.springframework.r2dbc.BadSqlGrammarException: execute; bad SQL grammar [UPDATE demo2 SET data = $1 WHERE demo2.pk = $2]
...
Caused by: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42804] column "data" is of type uuid but expression is of type character varying

Similar to the previous case,

postgres=# UPDATE demo2 SET data = 'e90469b6-efe3-4e12-a07d-25bba7f27a05' WHERE pk = 'foobar';
UPDATE 1

is actually working. Only

postgres=# UPDATE demo2 SET data = 'e90469b6-efe3-4e12-a07d-25bba7f27a05'::varchar WHERE pk = 'foobar';
ERROR:  column "data" is of type uuid but expression is of type character varying

can trigger the same error.

[mp911de]

Spring Data R2DBC converts the UUID to String because your domain model defines the primary key as String. Please use UUID to retain the more specific type.