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.tanzer@xxxxxxxxx>
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 | 1b | 2Which 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 | 1b | 2c | 1a | 2b | 1c | 2Throw 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 1b 2c (NULL)As that would be perfect for my purposes. Thanks in advance!Ken--AGENCY Software
A data system that puts you in control100% Free Softwarelearn more about AGENCY orfollow the discussion.
