I've recently changed my columns from "TIMESTAMP" to "TIMESTAMP WITH TIMEZONE".
Normally I would have a model class that would have a "LocalDateTime" but now I've changed to include timezone I use an "OffsetDateTime" to match the type used in the generated code by JOOQ.
I'm using the latest JOOQ and H2. H2 is in Postgres mode.
Upon trying to save into the USER_MODEL table I get an error.
Caused by: io.r2dbc.h2.H2DatabaseExceptionFactory$H2R2dbcDataException: Data conversion error converting "CHARACTER LARGE OBJECT to TIMESTAMP WITH TIME ZONE"; SQL statement:
select "id" from final table (merge into "user_model" using (select cast($1 as varchar(1000000000)) "email", cast($2 as varchar(1000000000)) "password", cast($3 as varchar(1000000000)) "role", cast($4 as varchar(1000000000)) "tier", cast($5 as varchar(1000000000)) "first_name", cast($6 as varchar(1000000000)) "last_name", cast($7 as boolean) "enabled", cast($8 as timestamp(9) with time zone) "created_at", cast($9 as timestamp(9) with time zone) "updated_at", cast($10 as smallint) "total_retries") "t" on "user_model"."id" = cast($11 as uuid) when matched then update set "user_model"."email" = $12, "user_model"."password" = $13, "user_model"."role" = $14, "user_model"."tier" = $15, "user_model"."first_name" = $16, "user_model"."last_name" = $17, "user_model"."enabled" = $18, "user_model"."created_at" = $19, "user_model"."updated_at" = $20, "user_model"."total_retries" = $21 when not matched then insert ("email", "password", "role", "tier", "first_name", "last_name", "enabled", "created_at", "updated_at", "total_retries") values ("t"."email", "t"."password", "t"."role", "t"."tier", "t"."first_name", "t"."last_name", "t"."enabled", "t"."created_at", "t"."updated_at", "t"."total_retries")) "user_model" [22018-230]
at io.r2dbc.h2.H2DatabaseExceptionFactory.convert(H2DatabaseExceptionFactory.java:60) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
at io.r2dbc.h2.client.SessionClient.query(SessionClient.java:138) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
at io.r2dbc.h2.H2Statement.lambda$execute$2(H2Statement.java:145) ~[r2dbc-h2-1.0.0.RELEASE.jar:1.0.0.RELEASE]
at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:113) ~[reactor-core-3.6.8.jar:3.6.8]
... 71 common frames omitted
Caused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting "CHARACTER LARGE OBJECT to TIMESTAMP WITH TIME ZONE"; SQL statement:
Here is the SQL to generate my table:
CREATE TABLE user_model (
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY ,
email TEXT NOT NULL CONSTRAINT user_model_email CHECK (LENGTH(email) <= 320 ),
password TEXT NOT NULL CONSTRAINT user_model_password CHECK (LENGTH(password) <= 50),
role TEXT NOT NULL CONSTRAINT user_model_role CHECK (LENGTH(role) <= 50),
tier TEXT NOT NULL CONSTRAINT user_model_tier CHECK (LENGTH(tier) <= 50),
first_name TEXT NOT NULL CONSTRAINT user_model_first_name CHECK (LENGTH(first_name) <= 50),
last_name TEXT NOT NULL CONSTRAINT user_model_last_name CHECK (LENGTH(last_name) <= 50),
enabled BOOLEAN NOT NULL,
created_at TIMESTAMP(9) WITH TIME ZONE NOT NULL,
updated_at TIMESTAMP(9) WITH TIME ZONE NOT NULL,
total_retries SMALLINT NOT NULL CONSTRAINT user_model_total_retries CHECK (total_retries >= 0)
);
UserModel
data class UserModel(
override var id: UUID? = null,
val email: String?,
var password: String,
var role: RoleDto,
var firstName: String?,
var lastName: String?,
var enabled: Boolean,
var createdAt: OffsetDateTime?,
var updatedAt: OffsetDateTime?,
var totalRetries: Long = 0,
var tier: UserTier
) : BaseModel(id)
The code I'm running is this. It takes the user model, maps it to a record and inserts it.
val record = ctx.newRecord(table, model)
val id: UUID = ctx.insertInto(table)
.set(record)
.onConflict()
.doUpdate()
.set(record)
.returning(idField)
.awaitFirst()
.getValue(idField)!!
Here are the values for the model and record printed out.
UserModel(id=null, email=email@gmail.com, password=abcd=, role=USER, firstName=Michael, lastName=Name, enabled=false, createdAt=2024-08-10T19:49:33.731172500+01:00, updatedAt=2024-08-10T19:49:33.731172500+01:00, totalRetries=0, tier=FREE)
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+
|ID |EMAIL |PASSWORD |ROLE |TIER |FIRST_NAME|LAST_NAME|ENABLED|CREATED_AT |UPDATED_AT |TOTAL_RETRIES|
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+
|{null}|*example@gmail.com|*yODttTr/abcd=|*USER|*FREE|*Michael |*Name|*false |*2024-08-10T19:49:33.731172500+01:00|*2024-08-10T19:49:33.731172500+01:00| *0|
+------+----------------------------+---------------------------------------------+-----+-----+----------+---------+-------+------------------------------------+------------------------------------+-------------+
What could be causing this issue? It worked fine when just using TIMESTAMP and LocalDateTime but I can't get this working with TIMESTAMP WITH TIMEZONE and OffsetDateTime.
jOOQ 3.19 didn't officially integration test this data type on R2DBC yet, see:
#17088 is a bug and will be fixed in jOOQ 3.20.0, 3.19.12, 3.18.19, and 3.17.28
Things seem to work out of the box in PostgreSQL, it seems. In H2, you could try Settings.setBindOffsetDateTimeType(true), which would change the DefaultOffsetDateTimeBinding's behaviour to not bind a formatted string but the OffsetDateTime value itself, instead.
Note, you're probably using H2 only to integration test. I suggest using testcontainers for this, instead, as it will allow you to integration test your entire application directly on PostgreSQL, and thus use all of its wonderful vendor-specific features. This is especially true when you're using R2DBC, which means you'll run into way more edge cases than if using JDBC, so better remove one element of complexity from your setups.