Array casting in where : unexpected behavior

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

Responses

Browse pgsql-sql by date

  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