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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
