Re: BUG #16615: Cannot determine type of Date for "is null" expression

Hi Tom,
Thank you for your reply.
1. Here is the comments in JDBC Postgres driver.
// We must use UNSPECIFIED here, or inserting a Date-with-timezone into a
// timestamptz field does an unexpected rotation by the server's TimeZone:
//
// We want to interpret 2005/01/01 with calendar +0100 as
// "local midnight in +0100", but if we go via date it interprets it
// as local midnight in the server's timezone:

// template1=# select '2005-01-01+0100'::timestamptz;
// timestamptz
// ------------------------
// 2005-01-01 02:00:00+03
// (1 row)

// template1=# select '2005-01-01+0100'::date::timestamptz;
// timestamptz
// ------------------------
// 2005-01-01 00:00:00+03
// (1 row)
2. I don’t understand why the type cannot be parsed in T_NullTest. It can be parsed when using the SQL below.
String sql = "select * from tb_user where (createdat = ?)”;
PreparedStatement statement = conn.prepareStatement(sql);
statement.setDate(1, new java.sql.Date(date.getTime()));
3. Thank you for your working around solution. It’s good! I really want the entire table but I will implement pagination when the argument is null. Java actually has Date as null.

> On Sep 13, 2020, at 22:42, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
>> 1. Postgres JDBC driver will always use Oid UNSPECIFIED(0) for Date type.
>> See PgPreparedStatement.java, in setDate function.
>
> You could ask the JDBC crew why they do that, although I suspect they
> have reasons.
>
>> 2. In Postgres server, it won't coerce the type to Date even if we provide
>> the type. See parse_expr.c in transformExprRecurse function.
>
> There's nothing to coerce it *to*.
>
> I'd suggest working around this with something like
>
> select * from tb_user where (?::date is null or createdat > ?)
>
> Although TBH that query looks pretty fishy to start with. Do you
> really want the entire table when the argument is null? Is it even
> possible for the argument to be null --- I doubt Java has such a
> thing as a null Date?
>
> regards, tom lane
>
>