JDBI can't bind PGInterval to PostgreSQL INTERVAL

Question

When attempting to update or insert a table containing an INTERVAL in PostgreSQL using JDBI, I'm getting the following error: org.jdbi.v3.core.statement.UnableToCreateStatementException: No argument factory registered for '1 years 0 mons 0 days 0 hours 0 mins 0.0 secs' of qualified type org.postgresql.util.PGInterval [...]

Minimal reproducible example:

• In Database:

CREATE TABLE testing (i INTERVAL);

• In Kotlin (Likely yields the same result in plain Java):

Jdbi.create(PGSimpleDataSource().apply {
        setURL([connection string goes here])
    }).withHandle<Int, SQLException> { handle: Handle ->
        handle.createUpdate("INSERT INTO testing(i) VALUES (:interval)")
                .bind("interval", PGInterval(1, 0, 0, 0, 0, 0.0))
                .execute()
    }

I've tried inserting it as a string since queries accept 'P1Y', but that gives me ERROR: column "i" is of type interval but expression is of type character varying, which would make sense if the first method worked.

It seems like it would only make sense for PGIntervals to be the expected type to work, since when obtaining intervals from a result set, a PGInterval is returned.

Although I somehow doubt it'll matter, I'm using:

• PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
• org.jdbi:jdbi3-core:3.18.1 + org.jdbi:jdbi3-kotlin:3.18.1 + org.postgresql:postgresql:42.2.19

Answer 1

I believe you should use PostgreSQL JDBI Plugin to work with PostgreSQL-specific types.