Postgres Pro
Downloads
Home   >   mailing lists

Re: [SQL] Always getting back a row, even with no results - Mailing list pgsql-sql

From Jonathan Moules
Subject Re: [SQL] Always getting back a row, even with no results
Date
Msg-id 15dd1ef824a.11e71b280106536.5163340769196231808@lightpear.com
Whole thread Raw
In response to Re: [SQL] Always getting back a row, even with no results  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: [SQL] Always getting back a row, even with no results  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-sql
Tree view
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?
Cheers,
Jonathan

---- On Fri, 11 Aug 2017 16:18:04 +0100 David G. Johnston<david.g.johnston@gmail.com> wrote ----
On Fri, Aug 11, 2017 at 6:57 AM, Jonathan Moules <jonathan-lists@lightpear.com> wrote:
This will of course return the two rows with that category. But I also want to be able to run the query with a non-existent cat and get a result of "null" for the id.

​untested​

​SELECT unnest( ARRAY( (​
 select id from my_table where cat = 50
​​ ) ) );

tested, self-contained, example:

SELECT unnest(ARRAY((SELECT col FROM ( VALUES (1), (2) ) vals (col) WHERE true)))

David J.



pgsql-sql by date:

Previous
From: Jonathan Moules
Date:
Subject: Re: [SQL] Always getting back a row, even with no results
Next
From: "David G. Johnston"
Date:
Subject: Re: [SQL] Always getting back a row, even with no results