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

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