Re: Understanding behavior of SELECT with multiple unnested columns

Hi,

You can try:

SELECT c1, c2 FROM
(
WITH a AS
(
SELECT row_number() OVER(),* FROM unnest(array['a','b', 'c', 'd']) c1
),
b AS
(
SELECT row_number() OVER(),* FROM unnest(array['1','2', '3']) c2
)
SELECT * FROM a LEFT JOIN b USING (row_number)
UNION
SELECT * FROM a RIGHT JOIN b USING (row_number)
ORDER BY row_number
) t

To simplify this you can wrap it in function what accepts two array
parameters...

Kind Regards,

Misa

2013/3/27 Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>

> I've been working on some queries involving multiple unnested columns. At
> first, I expected the number of rows returned would be the product of the
> array lengths, so that this query would return 4 rows:
>
> SELECT unnest2(array['a','b']),unnest2(array['1','2']);
>
> when in fact it returns 2:
>
> unnest2 | unnest2
> ---------+---------
> a | 1
> b | 2
>
> Which is all well and good. (Better, in fact, for my purposes.) But
> then this query returns 6 rows:
>
> SELECT unnest2(array['a','b','c']),unnest2(array['1','2']);
> unnest2 | unnest2
> ---------+---------
> a | 1
> b | 2
> c | 1
> a | 2
> b | 1
> c | 2
>
> Throw an unnested null column in and you get zero rows, which I also
> didn't expect:
>
> SELECT
> unnest2(array['a','b','c']),unnest2(array['1','2']),unnest(NULL::varchar[]);
> unnest2 | unnest2 | unnest
> ---------+---------+--------
> (0 rows)
>
>
> After some head scratching, I think I understand what to expect from these
> unnests, but I'm unclear of the logic behind what is going on. I'm hoping
> someone can explain it a bit. Also, on a practical level, would anyone
> know how to query so that SELECT
> unnest2(array['a','b','c']),unnest2(array['1','2']) would return three rows
> instead of six, like so:
>
> a 1
> b 2
> c (NULL)
>
> As that would be perfect for my purposes. Thanks in advance!
>
> Ken
>
>
>
>
> --
> AGENCY Software
> A data system that puts you in control
> 100% Free Software
> *http://agency-software.org/*
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
> Subscribe to the mailing list<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe>
> to
> learn more about AGENCY or
> follow the discussion.
>