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