Array casting in where : unexpected behavior
От | Achilleas Mantzios |
---|---|
Тема | Array casting in where : unexpected behavior |
Дата | |
Msg-id | 57454957.5070706@matrix.gatewaynet.com обсуждение исходный текст |
Ответы |
Re: Array casting in where : unexpected behavior
|
Список | pgsql-sql |
Good Morning list, we have a strange situation here, manifested in the following queries : Postgresql version : 9.3.10 dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.dbtag='observer')as qry; vals --------- {7078} {13916} {7078} {2054} {7078} {13916} {2054} {13916} {2054} {8844} {13916} {13916} {2054} {13916} {13916}{13916} {2054} {2054} {13916} {13916} {13916} {13916} {13916} {13916} {13916} (25 rows) dynacom=# dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.dbtag='observer')as qry WHERE vals[1]::int=8844; ERROR: invalid input syntax for integer: "19/10/2015" dynacom=# dynacom=# select vals[1]::int FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.dbtag='observer')as qry; vals ------- 7078 13916 7078 2054 7078 13916 2054 13916 2054 8844 13916 13916 2054 13916 13916 13916 2054 2054 1391613916 13916 13916 13916 13916 13916 (25 rows) dynacom=# dynacom=# select vals1 FROM (select vals[1]::int as vals1 from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid andfd.dbtag='observer') as qry; vals1 ------- 7078 13916 7078 2054 7078 13916 2054 13916 2054 8844 13916 13916 2054 13916 13916 13916 2054 2054 1391613916 13916 13916 13916 13916 13916 (25 rows) dynacom=# select vals1 FROM (select vals[1]::int as vals1 from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid andfd.dbtag='observer') as qry WHERE vals1=8844; ERROR: invalid input syntax for integer: "19/10/2015" dynacom=# ^^^ is this normal? Isn't vals1 guaranteed to be integer since its the type defined in the subselect ? However this seems to work : dynacom=# select * FROM (select vals from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.dbtag='observer')as qry WHERE vals[1] ~ E'^\\d+$' AND vals[1]::int=8844; vals -------- {8844} (1 row) dynacom=# -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
В списке pgsql-sql по дате отправления: