agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Array casting in where : unexpected behavior
Date: Wed, 25 May 2016 09:42:31 +0300
Message-ID: <57454957.5070706@matrix.gatewaynet.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (3+ messages) latest in thread
Message-ID: <57454957.5070706@matrix.gatewaynet.com>
Permalink: ../57454957.5070706@matrix.gatewaynet.com/
Also on: postgresql.org/message-id/57454957.5070706@matrix.gatewaynet.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: achill@matrix.gatewaynet.com
Subject: Re: Array casting in where : unexpected behavior
In-Reply-To: <57454957.5070706@matrix.gatewaynet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox