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

[Rowan Collins <rowan.collins@xxxxxxxxx>]

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]