Re: ERROR: invalid input syntax for type date: IS IT A BUG here? - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: ERROR: invalid input syntax for type date: IS IT A BUG here? |
Date | |
Msg-id | 55D7ACE6.9050309@aklaver.com Whole thread Raw |
In response to | Re: ERROR: invalid input syntax for type date: IS IT A BUG here? (AI Rumman <rummandba@gmail.com>) |
List | pgsql-general |
On 08/21/2015 03:47 PM, AI Rumman wrote: > 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. Per Toms post, try the SQL 3 query like this: 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' and dt = '2015-01-01'::date; You will probably see the same error then. > > Regards. > > On Fri, Aug 21, 2015 at 3:13 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: