X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 93ABFD80A8 for ; Wed, 12 Oct 2005 19:20:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54168-08 for ; Wed, 12 Oct 2005 22:20:51 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 9DE91D80FB for ; Wed, 12 Oct 2005 19:20:52 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9CMKlgd004704; Wed, 12 Oct 2005 18:20:47 -0400 (EDT) To: Collin Peters Cc: pgsql-sql@postgresql.org Subject: Re: Text->Date conversion in a WHERE clause In-reply-to: References: Comments: In-reply-to Collin Peters message dated "Wed, 12 Oct 2005 13:59:26 -0700" Date: Wed, 12 Oct 2005 18:20:47 -0400 Message-ID: <4703.1129155647@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200510/111 X-Sequence-Number: 22964 Collin Peters writes: > WHERE cust3 <> '' > AND cust3::text::timestamp > CURRENT_DATE - interval '1 month' > This results in the error 'ERROR: date/time field value out of range: > "052-44-5863"'. Now that is obviously not a valid date.... but there > is actually more to the where clause and the first part of it excludes > all rows where the user is not even the correct type, so the row which > includes the field '052-44-5863' should really not even be checked. Your mistake is in supposing that WHERE clauses are guaranteed to be evaluated in a particular order. Such guarantees are made only for a very few specific constructs such as CASE. See http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL regards, tom lane