Re: "could not determine data type of parameter" with timestamp - Mailing list pgsql-jdbc

Dave and Vladimir - please jump in if I've misunderstood how pgJDBC works in these cases.

Paraphrasing what Rémi wrote:

> Why is the driver not able to ignore the parameter type when we just want to compare it to null?

The pgJDBC driver can ignore the parameter type. Technically it passes the type as unspecified (0) and it passes the value as a string, but the effect is to ignore the parameter type.

The PostgreSQL backend cannot ignore the parameter type because it needs to know which RECEIVE function to call in order to interpret the value that is sent to it over the wire. (see https://www.postgresql.org/docs/current/static/sql-createtype.html) Because your program is using a PreparedStatement and because a PreparedStatement can be executed multiple times with different parameters every time, the backend must be ready to handle both null and non-null values. The PostgreSQL wire level v3 protocol allows pgJDBC to pass the parameter type as 0 (org.postgresql.core.Oid.UNSPECIFIED). However, setting the parameter type to 0 tells the PostgreSQL backend to determine the type of the parameter from the statement context.

In the case of the "where ? is null" query, there isn't enough context to determine the type and PostgreSQL itself sends the "ERROR: could not determine data type of parameter $1" message to pgJDBC.

The same error would happen with an ODBC driver or libpq or any other driver.

When your program calls ps.setNull(1, Types.VARCHAR), pgJDBC sends the parameter type as Oid.VARCHAR and the PostgreSQL backend doesn't have to figure it out from the context. It parses the query as "where ?::varchar is null" and is perfectly happy. It would be just as happy with ps.setNull(1, Types.DATE) which would be interpreted as "where ?::date is null" or some other type. (If PGProperty.STRING_TYPE is set to "unspecified" at connect time and you call rs.setNull(1, Types.VARCHAR), pgJDBC sends the null value as Oid.UNSPECIFIED and your workaround would fail.)

The flip side of that is that you can explicitly pass values as untyped parameters by using Types.OTHER and let PostgreSQL figure out the type for you. For example, "insert into mytable (myjsoncolumn) values (?)" works when you call ps.setObject(1, "{ \"name\":\"John\" }", Types.OTHER). With the same statement, ps.setString(1,  "{ \"name\":\"John\" }") would fail with a "ERROR: column " myjsoncolumn" is of type json but expression is of type character varying" message. And ps.setObject(1, "This is not JSON.", Types.OTHER) fails with "ERROR: invalid input syntax for type json". In all 3 cases, the backend knows that myjsoncolumn is a JSON column and deals with the parameter types/values accordingly.

> with "where birth_date > ?" ... ps.setNull(1, Types.VARCHAR) does not work 

This is an entirely different issue. In this case the PostgreSQL knows the type of the birth_date column is date and expects the parameter to match or for an implicit conversion between the parameter type and date to exist. That is why ps.setNull(1, Types.VARCHAR) fails with "ERROR: operator does not exist: date > character varying". For this case, you can tell pgJDBC to pass the parameter type as 0 by calling ps.setNull(1, Types.NULL) or you can call "ParameterMetaData pmeta = ps.getParameterMetaData()" to get the meta data for the parameters and then call "ps.setNull(1, pmeta.getParameterType(1))" to set the null with the type that PostgreSQL expects. pmeta.getParameterType(int) is generally the better option because it avoids the performance issues that Vladimir mentions in his presentation.

Note that getParameterMetaData() gets the parameter type information by asking the PostgreSQL backend to determine the types from the statement context. This means that calling getParameterMetaData() for the "where ? is null" case will fail with the  "ERROR: could not determine data type of parameter $1" message just like executing it with an unspecified null.

On Wed, Dec 6, 2017 at 7:38 AM, Dave Cramer <pg@fastcrypt.com> wrote:

On 6 December 2017 at 08:26, Rémi Aubel <remi.aubel@gmail.com> wrote:

Yes, ps.setNull(1, Types.TIMESTAMP) works.

But, once again, with the way my application works, when I bind the parameters, the query is already built. So, if my parameter is null, I have no way to guess its type.

I tried ps.setNull(1, Types.VARCHAR) as a fallback, and it works with a condition like "where ? is null". It does not work with a condition like "where birth_date > ?".

Maybe we could parse the query to use ps.setNull(1, Types.VARCHAR) when we have something like "? is null" (and a null bound parameter), but it seems to be weak (and a little bit dirty).

We would really like to avoid parsing the query.

 

Why is the driver not able to ignore the parameter type when we just want to compare it to null?

This is the way the extended protocol with PostgreSQL works.

Not much help for you but the api has contemplated this problem as there is the above mentioned method.

 Dave Cramer

davec@postgresintl.com

www.postgresintl.com

Rémi.

Le mer. 6 déc. 2017 à 01:58, Dave Cramer <pg@fastcrypt.com> a écrit :

So ps.setNull(1, Types.TIMESTAMP);

Doesn't work ?

Dave Cramer

davec@postgresintl.com

www.postgresintl.com

On 5 December 2017 at 15:42, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <remi.aubel@gmail.com> wrote:

Hello,

I know my problem has already been answered in this list (https://www.postgresql.org/message-id/CA%2BxsaB0EMDaNek0Oky9c17_8UX3-epWVP11%2BvTNgrAPoY2s9FA%40mail.gmail.com), but it was a long time ago and I would like to know if any other solution exists now.

​Not that I am aware.​

 

But this workaround is not really useful for me, because my application uses generated (dynamic) queries and targets multiple database types (not only PosgreSQL). So, when I know the targeted database, I do not know my parameter types anymore.

​I'd probably perform the null test in Java and pass the true/false boolean result along to the query:

SELECT * FROM my_table WHERE ?::bool;

ps.setBoolean(1, tsvar = null); //my Java is rusty but you get the idea.

That should work in any database.

David J.

​

--