Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1b5SWl-0007Tg-AZ for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2016 06:42:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1b5SWk-0000wA-Su for pgsql-sql@arkaria.postgresql.org; Wed, 25 May 2016 06:42:46 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1b5SWi-0000uh-WF for pgsql-sql@postgresql.org; Wed, 25 May 2016 06:42:45 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1b5SWb-0004Vo-Cm for pgsql-sql@postgresql.org; Wed, 25 May 2016 06:42:43 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id u4P6gVNs025838; Wed, 25 May 2016 09:42:32 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) To: pgsql-sql From: Achilleas Mantzios Subject: Array casting in where : unexpected behavior Message-ID: <57454957.5070706@matrix.gatewaynet.com> Date: Wed, 25 May 2016 09:42:31 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0 MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql