From: Tom Lane <tgl@sss.pgh.pa.us>
To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Text->Date conversion in a WHERE clause
Date: Wed, 12 Oct 2005 18:20:47 -0400
Message-ID: <4703.1129155647@sss.pgh.pa.us> (raw)
In-Reply-To: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
References: <df01c91b0510121359m1d7e67b5n41ee4d939798b69b@mail.gmail.com>
Collin Peters <cadiolis@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, cadiolis@gmail.com
Subject: Re: Text->Date conversion in a WHERE clause
In-Reply-To: <4703.1129155647@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox