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.
PG Bug reporting form <
noreply@postgresql.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
