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.tan...@gmail.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.tan...@agency-software.org > (253) 245-3801 > > Subscribe to the mailing > list<agency-general-requ...@lists.sourceforge.net?body=subscribe> > to > learn more about AGENCY or > follow the discussion. >
