Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux