From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Array casting in where : unexpected behavior |
Date: | 2016-05-25 06:42:31 |
Message-ID: | 57454957.5070706@matrix.gatewaynet.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | 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
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-05-25 12:29:24 | Re: Array casting in where : unexpected behavior |
Previous Message | David G. Johnston | 2016-05-24 23:43:04 | Re: INOUT text[],OUT text parameter handling problem |