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

Hi Adrian,

Thanks for replying here.

Actually, I modified the actual table name from my production where I
forgot to change the subtr value.

You can see the result "SELECT 558" in SQL 3 where it selected that many
rows.

Regards.

On Fri, Aug 21, 2015 at 3:13 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 08/21/2015 02:32 PM, AI Rumman wrote:
>
>> Hi All,
>>
>> I am using Postgresql 9.1 where have a partitioned table as below:
>>
>> events_20150101
>> events_20150102
>> events_20150103
>> ...
>> events_overflow
>>
>>
>> When I am running the following query it gives me result:
>>
>> *SQL 1: *
>>
>> select all 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'
>>
>>
> What is the result?
>
>
>> 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: ""*
>>
>
>
> production=# select substr('events_20150101', 18);
> substr
> --------
>
> (1 row)
>
> production=# select substr('events_20150101', 18)::date;
> ERROR: invalid input syntax for type date: ""
>
> Your substr is creating an empty str which cannot be cast to a date. I
> can't see how you could get a result from your first query, which is why I
> asked for what you are seeing.
>
>
>> However, explain is showing plan:
>>
>> Sort (cost=202.03..202.04 rows=1 width=64)
>> Sort Key:
>>
>> (pg_total_relation_size(((pg_stat_all_tables.relname)::text)::regclass))
>> -> Subquery Scan on pg_stat_all_tables (cost=201.93..202.02
>> rows=1 width=64)
>> -> HashAggregate (cost=201.93..201.99 rows=1 width=136)
>> -> Nested Loop Left Join (cost=0.00..201.92 rows=1
>> width=136)
>> -> Nested Loop (cost=0.00..194.23 rows=1
>> width=132)
>> Join Filter: (c.relnamespace = n.oid)
>> -> Seq Scan on pg_namespace n
>> (cost=0.00..1.39 rows=1 width=68)
>> Filter: ((nspname <> ALL
>> ('{pg_catalog,information_schema}'::name[])) AND (nspname !~
>> '^pg_toast'::text) AND (nspname = 'partitions'::name))
>> -> Seq Scan on pg_class c
>> (cost=0.00..192.77 rows=6 width=72)
>> Filter: ((relkind = ANY
>> ('{r,t}'::"char"[])) AND (relname !~~ '%overflow'::text) AND
>> ((substr((relname)::text, 18))::date = '2015-01-01'::date))
>> -> Index Scan using pg_index_indrelid_index
>> on pg_index i (cost=0.00..7.66 rows=2 width=8)
>> Index Cond: (c.oid = indrelid)
>>
>> Again, if I create a table and run the query it runs:
>>
>> *SQL 3:*
>>
>> create table dba.tbl_list as select all 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' ;
>> SELECT 558
>>
>> \d+ dba.tbl_list
>> Table "dba.tbl_list"
>> Column | Type | Modifiers | Storage | Description
>> ---------+--------+-----------+---------+-------------
>> relname | name | | plain |
>> s | bigint | | plain |
>> dt | date | | plain |
>> Has OIDs: no
>>
>>
>> *SQL 4:*
>>
>> select * from dba.tbl_list where dt = '2015-01-01';
>> relname | s | dt
>> ---------------------------+------------+------------
>> events_20150101 | 1309966336 | 2015-01-01
>> (1 row)
>>
>> Why the 2nd query is showing error? Is it a bug? Or am I doing any silly?
>> Any advice, please.
>>
>> Thanks & Regards.
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>