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

I think that clearly it is getting "pg_statistic" from the pg_catalog
schema and feeding it to the query. So "tatistic" gets extracted, being
the last 8 characters. And then "tati" is fed to the YYYY part of the
to_date function. But I already specify the table_schema to be the one
that I want. So those internal pg_* views shouldn't even show up in the
query.

Brian

On Tue, Oct 8, 2013 at 1:50 PM, Brian Wong <bwong(at)imageworks(dot)com> wrote:

> I'm posting this question to pgsql-general<http://www.postgresql.org/list/pgsql-general/>.
> Hopefully someone can share some insights with me.
>
> I have a bunch of tables in the database and in a separate schema. The
> tables' names are in this format:
>
> ???_???_???_YYYYMMDD
>
> where the last 8 characters is a date.
>
> *When I query either the information_schema.tables or pg_tables
> extracting the last 8 characters out and converting it to a date, it works:
> *
>
> select table_name, to_date(right(table_name, 8), 'YYYYMMDD') blah
> from information_schema.tables
> where table_schema = '????'
> and table_catalog = '????';
>
> *But as soon as I reference it in the where clause, it gives a weird
> error:*
>
> 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?
>
> Brian
>