Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhAgd-0005ZJ-KH for pgsql-sql@arkaria.postgresql.org; Mon, 14 Aug 2017 08:25:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhAgc-00016q-Ux for pgsql-sql@arkaria.postgresql.org; Mon, 14 Aug 2017 08:25:22 +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 1dhAfc-0007jF-V8 for pgsql-sql@postgresql.org; Mon, 14 Aug 2017 08:24:21 +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 1dhAfZ-0006Rp-Dv for pgsql-sql@postgresql.org; Mon, 14 Aug 2017 08:24:19 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id v7E8OB2F010775 for ; Mon, 14 Aug 2017 11:24:12 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) Subject: Re: Always getting back a row, even with no results To: pgsql-sql@postgresql.org References: <15dd19520d2.c2f4c716103038.4559674295652037989@lightpear.com> <15dd1ef824a.11e71b280106536.5163340769196231808@lightpear.com> From: Achilleas Mantzios Message-ID: Date: Mon, 14 Aug 2017 11:24:11 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------13C9ADD5A6DDB01C67B33C0C" Content-Language: en-US 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 This is a multi-part message in MIME format. --------------13C9ADD5A6DDB01C67B33C0C Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > wrote: > > On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules >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 --------------13C9ADD5A6DDB01C67B33C0C Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
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> wrote:
On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules <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
--------------13C9ADD5A6DDB01C67B33C0C--