Understanding behavior of SELECT with multiple unnested columns

[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       | 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.tanzer@xxxxxxxxxxxxxxxxxxx

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.