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

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