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.