Re: Text->Date conversion in a WHERE clause

You seem to be assuming that conjuncts in the where clause are
processed in order, a la many programming languages (this is sometimes
called "short circuiting").  I don't think this is so in SQL, else many
optimizations would not be possible.  I have even see the planner break
up and rearrange complex disjunction/conjunction combinations.

In your case, I would use a subquery to filter down to rows where the
column in question is interpretable as a date, then do your date
comparison in the outer select.  Thus:

   select *
   from (select * from foo
        where ... conditions to determine whether cust3 is a date ...) as
dateCusts
   where cust3::text::timestamp > CURRENT_DATE - interval '1 month';

- John D. Burger
   MITRE

> I have a table that has some columns which store 'custom' fields so the
> content varies according to the user that the row belongs to.  For one
> of the groups of users the field is a date (the type of the field is
> 'text' though).  I'm trying to perform a query where it only returns
> values in a certain date range so in the WHERE clause I have
>
> 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.
>
> My main confusion lies in the assumption I made that the offending row
> would not even be included as it should have already been discarded.
> Is this not the case?    How can I overcome this problem?  There
> appears to be no isDate() function in postgresql like there is in sql
> server.