Re: Always getting back a row, even with no results

On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules <
jonathan-lists(at)lightpear(dot)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[]
)
);

David J.