Re: ERROR: invalid value "????" for "YYYY"

On 08/10/2013 21:50, Brian Wong wrote:
> select table_name, to_date(right(table_name, 8), 'YYYYMMDD') blah
> from information_schema.tables
> where table_schema = '????'
> and table_catalog = '????'
> *and to_date(right(table_name, 8), 'YYYYMMDD') is not null;*
>
> *ERROR: invalid value "tati" for "YYYY"*
> DETAIL: Value must be an integer.
>
> It seems like some strange values were passed into the to_date
> function, but I'm seeing that the rightmost 8 characters of all the
> table names are dates. So only date strings are passed to the to_date
> function. Absolutely nothing containing the string "tati" is passed
> to the to_date function. What is going on? Is that a bug?

Unless I'm much mistaken, there is no guarantee that the conditions in a
WHERE clause will be checked in any particular order, because SQL does
not specify a procedural recipe, only a logical one. If for whatever
reason the query planner decides to check the condition involving
to_date first, it will have to evaluate it for all rows in the table,
leading to this error. It works fine in the SELECT clause because that
happens logically after all filtering has taken place.

I'm not sure if there are easier ways, but one way to force the order
would be to restrict the set of tables in a sub-query or CTE first, and
then check whatever you need about the date:

With tables_with_dates As (
select table_name, to_date(right(table_name, 8), 'YYYYMMDD') blah
from information_schema.tables
where table_schema = '????'
and table_catalog = '????'
)
Select table_name, blah
Where blah > '2013-01-01'::date

--
Rowan Collins
[IMSoP]