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.
