Обсуждение: [GENERAL] Implicit typecasting to numeric in psql
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
Payal Singh <payals1@umbc.edu> writes: > 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; Cross-type comparisons are legal, in general, so this is a legal query. The fact that no rows could match is not relevant to that. > 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 Well, sure. That number doesn't fit in an integer. > db=# explain select * from users where userid = '21474737377373737373'; > ERROR: value "21474737377373737373" is out of range for type integer The reason this fails is that the quoted literal initially has type "unknown", and the parser's heuristic for resolving the unknown is, in this case, to give it the same type as the operator's other input. So then it tries to convert 21474737377373737373 to integer. See https://www.postgresql.org/docs/current/static/typeconv-oper.html particularly rule 3f. > 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: That's specified in the description of constants, https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS (see 4.1.2.6 about numeric constants). Numeric constants don't start out as "unknown" the way quoted literals do, because it's possible to make a reasonable determination of their type without any context. regards, tom lane