Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kHTDk-0005Bb-Ng for pgsql-bugs@arkaria.postgresql.org; Sun, 13 Sep 2020 14:43:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kHTDh-0001on-SK for pgsql-bugs@arkaria.postgresql.org; Sun, 13 Sep 2020 14:43:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kHTDh-0001og-Hi for pgsql-bugs@lists.postgresql.org; Sun, 13 Sep 2020 14:43:09 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kHTDb-0002lz-8n for pgsql-bugs@lists.postgresql.org; Sun, 13 Sep 2020 14:43:08 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 08DEgqkF749017; Sun, 13 Sep 2020 10:42:57 -0400 From: Tom Lane To: yuanhang.zheng@qq.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16615: Cannot determine type of Date for "is null" expression In-reply-to: <16615-1f85b569d6a34277@postgresql.org> References: <16615-1f85b569d6a34277@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Sun, 13 Sep 2020 00:28:45 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <749015.1600008172.1@sss.pgh.pa.us> Date: Sun, 13 Sep 2020 10:42:52 -0400 Message-ID: <749016.1600008172@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form 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