[4.0.0] Using ::timestamptz with the Postgres Client

[Yuce Tekol]

Hi all,

I'm using the Vert.x 4 reactive PG client and have to map a string to SQL timestamptz.

The Postgres table looks like this:

CREATE TABLE table1 (
    item_id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ
);

(The examples below are in Kotlin, I can rewrite them in Java if it's more convenient)

Using ?::timestamptz to let PG convert the string works OK with JDBC:

val query =
    """"
    INSERT INTO table1 (created_at)
    VALUES (?::timestamptz)
    """"
DriverManager.getConnection(url).use { conn ->
    conn.prepareStatement(query).use { stmt ->
        stmt.setObject(1, "2017-12-03T10:15:30Z")
        stmt.execute()
    }
}

I can't seem to do the same with the Vert.x PG client. I've tried:

val query =
    """"
    INSERT INTO table1 (created_at)
    VALUES ($1::timestamptz)
    """"
client.getConnection().compose { conn ->
    conn
        .prepare(query)
        .execute(Tuple.of("2017-12-03T10:15:30Z"))
        .onComplete {
            if (it.succeeded()) println("Query ran OK")
            else println("Query failed: ${it.cause()}")
        }
}

I get the following error with the code above:

io.vertx.core.impl.NoStackTraceThrowable: Parameter at position[0] with class = [java.lang.String] and value = [2017-12-03T10:15:30Z] can not be coerced to the expected class = [java.time.OffsetDateTime] for encoding.

As far as I see, Vert.x ignores the ::timestamptz part and directly compares the  parameter type with the table schema.

Is there a workaround I can use to bypass Vert.x's type check or any other way to let Postgres handle the query?

I can create a full project if it helps.

Thanks a lot in advance!
Yuce

[Yuce Tekol]

 The following query with ::timestamptz works with the vertx-jdbc-client as well. Since vertx-pg-client is at  https://github.com/eclipse-vertx/vertx-sql-client, should I open a ticket there?

[Asher Tarnopolski]

i think you should put a 
java.time.OffsetDateTime object in your tuple and not a string.

[Yuce Tekol]

Yeah, but both the query and the actual placeholder are user-defined (I've used constants just to illustrate the problem). I could read the schema of the table and convert user-defined placeholders to the correct types on the fly  but I'm trying to avoid magic and let Postgres handle the conversion. Also even if I convert the timestamp string to timestampz, the user can still use ::timestampz in their query which would result in error.

Both JDBC and vertx-jdbc-client can handle the ::timestampz conversion, so either I am using vertx-pg-client wrong or there's something missing.

[Asher Tarnopolski]

did you try to submit your query without adding ::timestampz but ti put a java.time.OffsetDateTime object in your prepared statement's tuple rather than a string? what was the outcome?

--
You received this message because you are subscribed to a topic in the Google Groups "vert.x" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/vertx/vO2Y8DznNEA/unsubscribe.
To unsubscribe from this group and all its topics, send an email to vertx+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/9b9eb7cc-79ee-4a1c-b6b8-c0d443230276n%40googlegroups.com.

[Yuce Tekol]

Yes I did. That works just fine.

[Asher Tarnopolski]

I'm not sure i follow re user defined placeholders. your column is of a certain type, you can't just throw anything there. so you either use the valid type in java and vertx do the trick or wrap your string in a postgres function like TO_DATE(..) to do the parsing on the db site. what do i miss?

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/2467df65-ad51-4b62-8ac6-90bed98167f6n%40googlegroups.com.

[Yuce Tekol]

Thanks, using `TO_DATE` works. Why doesn't ::timestampz work though? It is valid Postgres SQL. JDBC and vertx-jdbc-client do the expected thing and pass the string I give them to Postgres. Is that a design choice for vertx-pg-client or something missing?

Anyway I've created a sample project at: https://github.com/yuce/vertx-pg-client-timestamptz which has the TO_DATE and ::timestamptz variants.

[Asher Tarnopolski]

i am no sure it doesn't work. did you check it's not getting added by vertx internally when you send an OffsetData in your tuple?

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/b53077b9-ecb9-45d8-8759-6e7f654dcd0an%40googlegroups.com.

[Yuce Tekol]

Thanks for your time. I've created an issue at https://github.com/eclipse-vertx/vertx-sql-client/issues/869