Re: "could not determine data type of parameter" with timestamp

>So, if my parameter is null, I have no way to guess its type.

TL;DR: always use typed nulls in JDBC; use consistent data types (avoid
date/varchar flips) for the same sql text; use "?::timestamptz" for edge
cases.

>I tried ps.setNull(1, Types.VARCHAR) as a fallback

Please, avoid that. It will backfire with both PostgreSQL and Oracle DB.

1) That's very common pitfall with SQL and JDBC.
nulls are typed in SQL.

Suppose you have a couple of procedures: "procedure test(v varchar)" and
"procedure test(v number)".

ps.setNull(1, Types.VARCHAR) and ps.setNull(1, Types.NUMERIC) should result
in execution of _different_ procedures.

JDBC driver has no way which one do you want to execute if you "just pass
null".

Unfortunately, we cannot go very strict there since everybody just assumes
"date is pretty much the same thing as timestamp", so in case client passes
"timestamp, date, etc", pgjdbc just passes "unknown type" formatted as full
timestamp and assumes backend would parse and use accordingly. For
instance, if just a date was expected, it would just ignore time/timezone
part.

That backfires for the case "select ?" when backend has no way to tell what
was the "expected bind type". Of course it cannot identify datatype. I have
no idea how that can be healed and I do think the current behaviour is sane.

2) On top of that, different datatypes might result in different execution
plans, thus JDBC/DB has to adapt to "new datatypes" in case you flip types
of bind parameters for the same SQL text back and forth.
You can find a bit on that around slides 25-27:
https://www.slideshare.net/VladimirSitnikv/postgresql-and-jdbc-striving-for-high-performance/25

Vladimir