From: | Payal Singh <payals1(at)umbc(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Implicit typecasting to numeric in psql |
Date: | 2017-05-02 19:45:46 |
Message-ID: | CAK4ounzasAz0JKWm7yNPT9FGxP6LWNn58db9sqzxp5Qiq1XwvQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sylvain Marechal | 2017-05-02 19:52:02 | Re: BDR replication and table triggers |
Previous Message | Julien Rouhaud | 2017-05-02 19:45:26 | Re: [GENERAL] Column rename in an extension update script |