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'
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: ""*
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.
