Search Postgresql Archives

Implicit typecasting to numeric in psql

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

 



Hi,

I have a table with an integer column 'userid'. But I am not seeing an out of range error when trying to get an id larger than possible in integer:

db=# explain select * from users where userid = 21474836472871287898765456789::numeric;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..4047620.36 rows=431750 width=301)
   Filter: ((userid)::numeric = '21474836472871287898765456789'::numeric)
(2 rows)

I do see an error when using a bind variable though, just not in psql. Also, when putting it in quotes or explicitly casting it to integer, I do get the our of range message:

db=# select * from users where userid = 21474836472344567898765456789::integer;
ERROR:  integer out of range

db=# explain select * from users where userid = '21474737377373737373';
ERROR:  value "21474737377373737373" is out of range for type integer
LINE 1: ...lain select * from users where userid = '214747373...
                                                             ^
db=# explain select * from users where userid = '2147';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Index Scan using userid_pkey on users  (cost=0.57..8.59 rows=1 width=301)
   Index Cond: (userid = 2147)

It seems when on psql and querying for a numeric type, postgres is not checking the type of the column, but instead converting into the numeric type that best matches the length:

db=# explain select * from users where userid = 2147473737737373;
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Index Scan using userid_pkey on users  (cost=0.57..8.59 rows=1 width=301)
   Index Cond: (userid = '2147473737737373'::bigint)
(2 rows)

db=# explain select * from users where userid = 21474737377373737373;
                            QUERY PLAN                            
------------------------------------------------------------------
 Seq Scan on users  (cost=0.00..4047620.36 rows=431750 width=301)
   Filter: ((userid)::numeric = '21474737377373737373'::numeric)
(2 rows)

Why is it that postgres checks the data type of the column when value is in quotes vs not checking when no quotes are used?

Thanks,

--
Payal Singh

[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