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