Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 16E82632A38 for ; Thu, 2 Apr 2009 12:10:14 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 09411-07 for ; Thu, 2 Apr 2009 12:10:00 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id ECC74633197 for ; Thu, 2 Apr 2009 12:09:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n32F9jVN023538; Thu, 2 Apr 2009 11:09:45 -0400 (EDT) To: emilu@encs.concordia.ca cc: PostgreSQL SQL List Subject: Re: ibatis with overlaps query In-reply-to: <49D4C551.3030702@encs.concordia.ca> References: <49D4C551.3030702@encs.concordia.ca> Comments: In-reply-to Emi Lu message dated "Thu, 02 Apr 2009 10:01:53 -0400" Date: Thu, 02 Apr 2009 11:09:44 -0400 Message-ID: <23537.1238684984@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.072 tagged_above=0 required=5 tests=AWL=0.072 X-Spam-Level: X-Archive-Number: 200904/14 X-Sequence-Number: 32360 Emi Lu writes: > With ibatis, do overlap checking: > (1) select (DATE #begin_date#, DATE #end_date#) overlaps > (DATE '2008-01-01', DATE '2009-01-01') > . #begin_date# is varchar > . #end_date# is varchar > Cause: java.sql.SQLException: ERROR: syntax error at or near "$4" > However, when I updated the query to > (2) select (#begin_date#::DATE, #end_date#::DATE) overlaps > (DATE '2008-01-01', DATE '2009-01-01') > It works. I am bit confused why (1) does not work, but (2) does? The syntax DATE 'foo' (or more generally, typename 'foo') works only for string-literal constants 'foo'. It looks like what your frontend is actually sending is a parameter symbol, like DATE $1, which is a syntax error. There is more about this in the manual, sections 4.1.2.5 and 4.2.8: http://www.postgresql.org/docs/8.3/static/sql-syntax.html regards, tom lane