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 <bw...@imageworks.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 >
