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
