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