Re: ERROR: invalid input syntax for type date: IS IT A BUG here?

AI Rumman <rummandba(at)gmail(dot)com> writes:
> But when I run the following one, it gives me error:
> *SQL 2: *

>> select * as ts
>> from
>> (
>> select relname, pg_total_relation_size(relname::text) as s,
>> substr(relname,18)::date as dt from pg_stat_user_tables where schemaname =
>> 'partitions' and relname not like '%overflow' order by
>> pg_total_relation_size(relname::text) desc
>> ) as q
>> where dt = '2015-01-01'::date;
>> *ERROR: invalid input syntax for type date: ""*

I don't find that especially astonishing: there are certainly rows in
pg_stat_user_tables for which "substr(relname,18)::date" will fail.

Your first query managed to dodge that with WHERE restrictions, but here
you've added a WHERE restriction that depends on being able to evaluate
that very expression. And no, there is no guarantee about the order of
evaluation of WHERE clauses. (An OFFSET 0 in the sub-select might help
though.)

regards, tom lane