Array casting in where : unexpected behavior

Поиск
Список
Период
Сортировка
Искать
От
Achilleas Mantzios
Тема
Array casting in where : unexpected behavior
Дата
в 06:42:45
Msg-id
57454957.5070706@matrix.gatewaynet.com
Список
Дерево обсуждения
Array casting in where : unexpected behavior Achilleas Mantzios <achill@matrix.gatewaynet.com>
Re: Array casting in where : unexpected behavior "David G. Johnston" <david.g.johnston@gmail.com>
Re: Array casting in where : unexpected behavior Achilleas Mantzios <achill@matrix.gatewaynet.com>
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 13916 13916 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 and fd.dbtag='observer') as qry; vals1
-------  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=# select vals1 FROM (select vals[1]::int as vals1 from fb_forms_defs fd, fb_reports_dets rd where fd.id=rd.fdid and fd.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 по дате отправления
От: David G. Johnston
Дата:
От: David G. Johnston
Дата:
FAQ