ERROR: invalid value "????" for "YYYY" - Mailing list pgsql-general

From Brian Wong
Subject ERROR: invalid value "????" for "YYYY"
Date
Msg-id CA+dZhz+ehBJ2agCT2Ra8JbgvfvYPTXO3UMqx7Ej-Cfy52DktoA@mail.gmail.com
Whole thread Raw
Responses Re: ERROR: invalid value "????" for "YYYY"  (Brian Wong <bwong@imageworks.com>)
Re: ERROR: invalid value "????" for "YYYY"  (Rowan Collins <rowan.collins@gmail.com>)
Re: ERROR: invalid value "????" for "YYYY"  (Steve Crawford <scrawford@pinpointresearch.com>)
List pgsql-general
I'm posting this question to 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

pgsql-general by date:

Previous
From: Thomas Kellerer
Date:
Subject: Re: need elegant way to store and query tables with variable headers
Next
From: Adam Mackler
Date:
Subject: Re: Looking for some advise on training materials