Postgres Pro
Downloads
Home   >   mailing lists

Re: Understanding behavior of SELECT with multiple unnested columns - Mailing list pgsql-general

From Misa Simic
Subject Re: Understanding behavior of SELECT with multiple unnested columns
Date
Msg-id CAH3i69kt+poyHjzYgWRzscacoPqMMzxUKGsNb9yUOjQRaSWa0w@mail.gmail.com
Whole thread Raw
In response to Understanding behavior of SELECT with multiple unnested columns  (Ken Tanzer <ken.tanzer@gmail.com>)
List pgsql-general
Tree view
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@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

learn more about AGENCY or
follow the discussion.

pgsql-general by date:

Previous
From: Gavin Flower
Date:
Subject: Re: Understanding behavior of SELECT with multiple unnested columns
Next
From: Thomas Kellerer
Date:
Subject: Re: Why does Postgres allow duplicate (FK) constraints