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

AI Rumman <rummandba@gmail.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