Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 41C6E9FBA1A for ; Tue, 16 Oct 2007 17:54:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 28245-10 for ; Tue, 16 Oct 2007 17:53:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id A6D859FB9EC for ; Tue, 16 Oct 2007 17:53:59 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l9GKruwd023447; Tue, 16 Oct 2007 16:53:56 -0400 (EDT) To: "Tena Sakai" cc: pgsql-sql@postgresql.org Subject: Re: what's wrong with my date comparison? In-reply-to: References: Comments: In-reply-to "Tena Sakai" message dated "Tue, 16 Oct 2007 11:00:08 -0700" Date: Tue, 16 Oct 2007 16:53:56 -0400 Message-ID: <23446.1192568036@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/78 X-Sequence-Number: 29450 "Tena Sakai" writes: > I was missing quotes. > It must have evaluated 2007-10-02 and used it as a > numerical constant 1995. Actually, what you got was a *textual* comparison between '1995' and the timestamp converted to text, which makes even less sense. FWIW, as of PG 8.3 you'll get an error: regression=# select * from timestamp_tbl where d1 > 2007-10-02; ERROR: operator does not exist: timestamp without time zone > integer LINE 1: select * from timestamp_tbl where d1 > 2007-10-02; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. We've seen too many people get burnt by variants of this problem... regards, tom lane