Thread: ERROR: invalid input syntax for type date: IS IT A BUG here?
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.
AI Rumman <rummandba@gmail.com> writes:
> 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: ""*
I don't find that especially astonishing: there are certainly rows in
pg_stat_user_tables for which "substr(relname,18)::date" will fail.
Your first query managed to dodge that with WHERE restrictions, but here
you've added a WHERE restriction that depends on being able to evaluate
that very expression. And no, there is no guarantee about the order of
evaluation of WHERE clauses. (An OFFSET 0 in the sub-select might help
though.)
regards, tom lane
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
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.
Regards.
On Fri, Aug 21, 2015 at 3:13 PM, Adrian Klaver <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.*SQL 3:*
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:
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
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