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

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(at)fastcrypt(dot)com> wrote:

>
>
>
>
> On 6 December 2017 at 08:26, Rémi Aubel <remi(dot)aubel(at)gmail(dot)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(at)postgresintl(dot)com
> www.postgresintl.com
>
>> Rémi.
>>
>> Le mer. 6 déc. 2017 à 01:58, Dave Cramer <pg(at)fastcrypt(dot)com> a écrit :
>>
>>> So ps.setNull(1, Types.TIMESTAMP);
>>>
>>> Doesn't work ?
>>>
>>> Dave Cramer
>>>
>>> davec(at)postgresintl(dot)com
>>> www.postgresintl.com
>>>
>>> On 5 December 2017 at 15:42, David G. Johnston <
>>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>
>>>> On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> I know my problem has already been answered in this list (
>>>>> https://www.postgresql.org/message-id/CA%2BxsaB0EMDaNek0Oky
>>>>> 9c17_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.
>>>> ​
>>>>
>>>
>>> --
>>
>>
>>