>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.
Vladimir
