agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Always getting back a row, even with no results
Date: Mon, 14 Aug 2017 11:24:11 +0300
Message-ID: <e353d4eb-f27a-2e4a-79eb-9dfd84fef703@matrix.gatewaynet.com> (raw)
In-Reply-To: <CACpWLjMV0+Qi35X5-SRK_qUKqkzn7oq+woo5P25mXZF4e8GeFA@mail.gmail.com>
References: <15dd19520d2.c2f4c716103038.4559674295652037989@lightpear.com>
<CAKFQuwYxL33=Az3EiH-=1-R1cHLSs98zUDvtcLgZDURoc3wYHQ@mail.gmail.com>
<15dd1ef824a.11e71b280106536.5163340769196231808@lightpear.com>
<CAKFQuwavg0Pu5-LCVd4Rq5md=KwrD+geMP9arxf2Z8c1cGXFaQ@mail.gmail.com>
<CACpWLjMV0+Qi35X5-SRK_qUKqkzn7oq+woo5P25mXZF4e8GeFA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
On 12/08/2017 05:41, Michael Moore wrote:
> with x as
> (select id,1 mark from my_table where cat = 3
> union all
> select null,0 mark)
> select id from x where mark = (select max(mark) from x) alia;
> Don't have SQL right now so can't test it.
>
>
> On Fri, Aug 11, 2017 at 8:43 AM, David G. Johnston <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote:
>
> On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules <jonathan-lists@lightpear.com <mailto:jonathan-lists@lightpear.com>>wrote:
>
> Hi David,
> I'm afraid I don't really understand this response (I've not done much with arrays), but it doesn't seem to work for my purpose.
>
> No NULL is returned if there is no result (i.e. cat = 50); instead, there's simply no rows.
>
> What aspect of Arrays is this trying to take advantage of?
>
>
> My bad, I had tested the "false" version with a single record, without the array, and it indeed works. But the scalar subselect prevents the inner query from returning more than one row. I
> added the array to handle the multiple rows setup (which required testing the true path) and forgot to go back and test the false path.
>
> The idea of the array was to keep the inner subquery scalar.
>
> The following works on 9.5 - not positive whether it will on 10 though, we made some changes in this area.
>
> SELECT
> unnest(
> COALESCE(
> (SELECT array_agg(col) FROM ( VALUES (1), (2) ) vals (col) WHERE true),
> ARRAY[null]::int[]
> )
> );
>
I liked this!
Also an array solution, since it was mentioned, first an existing example, then a non-existing :
smadev dynacom=# select unnest(CASE WHEN arr='{}' THEN '{null}' ELSE arr END) FROM (select ARRAY(select id from flags where id=221) as arr) qry;
unnest
--------
221
(1 row)
smadev dynacom=#
smadev dynacom=# select unnest(CASE WHEN arr='{}' THEN '{null}' ELSE arr END) FROM (select ARRAY(select id from flags where id=-221) as arr) qry;
unnest
--------
(1 row)
smadev dynacom=#
> David J.
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
view thread (9+ messages) latest in thread
Message-ID: <e353d4eb-f27a-2e4a-79eb-9dfd84fef703@matrix.gatewaynet.com>
Permalink: ../e353d4eb-f27a-2e4a-79eb-9dfd84fef703@matrix.gatewaynet.com/
Also on: postgresql.org/message-id/e353d4eb-f27a-2e4a-79eb-9dfd84fef703@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: Always getting back a row, even with no results
In-Reply-To: <e353d4eb-f27a-2e4a-79eb-9dfd84fef703@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