From: | Brian Wong <bwong(at)imageworks(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Cottalango Leon <loorthu(at)imageworks(dot)com> |
Subject: | ERROR: invalid value "????" for "YYYY" |
Date: | 2013-10-08 20:50:36 |
Message-ID: | CA+dZhz+ehBJ2agCT2Ra8JbgvfvYPTXO3UMqx7Ej-Cfy52DktoA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Mackler | 2013-10-08 21:04:40 | Re: Looking for some advise on training materials |
Previous Message | Thomas Kellerer | 2013-10-08 18:52:00 | Re: need elegant way to store and query tables with variable headers |