Turning a PostgreSQL array to rows

I’ve spent the last few blog posts in this series talking about PostgreSQL’s arrays — how to create them, query them, and even create them with the ARRAY function.  But there are cases in which I would like to do the opposite — turn a PostgreSQL array to rows.

There are a number of reasons why I might want to do this. For example, perhaps I want to find the intersection between two arrays.  The INTERSECT operator in PostgreSQL can easily do that with two sets of rows — but there isn’t any equivalent for arrays.  Similarly, the UNION operator lets me join two sets of rows, but nothing like that exists for arrays.  And of course, there are times when I might want to take the elements of an array and run a JOIN between them and the rows of an existing table.

The key to all of these is the UNNEST function.  You need to be a bit careful when you’re using UNNEST, because (as always with computers) PostgreSQL will do what you tell it to do, not necessarily what you want it to do.  For example, if I have the following table:

select * from foo;
+----+---------------+
| id | stuff         |
+----+---------------+
| 1  | {abc,def,abc} |
| 2  | {abc,xyz}     |
+----+---------------+

Let’s say that I use UNNEST on our first row:

[local]/reuven=# SELECT UNNEST(stuff) FROM Foo where id = 1;
+--------+
| unnest |
+--------+
| abc    |
| def    |
| abc    |
+--------+

Sure enough, the three elements of “stuff” where id is 1 have now become three rows.  We get the results as a set of rows, which we can then capture in a table, query in a subquery, or turn back into an array, if we want:

SELECT ARRAY(SELECT UNNEST(stuff) FROM Foo where id = 1);
+---------------+
| array         |
+---------------+
| {abc,def,abc} |
+---------------+

This example might seem pointless, but it raises some additional possibilities.  For example, let’s say that I want to ensure that the elements of my array are unique.  I can do that by applying the DISTINCT operation on the result of UNNEST, and then packing those distinct rows back into an array:

SELECT ARRAY(SELECT DISTINCT UNNEST(stuff) FROM Foo where id = 1);
+-----------+
| array     |
+-----------+
| {abc,def} |
+-----------+

It’s this sort of operation that has led me to really love PostgreSQL arrays.  If I use them as actual arrays, then I feel bad, as though I’ve betrayed the entire world of normalized, relational databases. But if I think of arrays as frozen sets of rows, which I can then unfreeze (with UNNEST), manipulate, join, or otherwise modify, and then re-freeze (with ARRAY) as necessary, then things suddenly get much clearer and easier.

I’m in the last weeks of my PhD research and writing, and I’ve been using no small number of arrays in my research and analysis.  I wouldn’t dream of using arrays to store actual combinations of data that could otherwise be normalized and stored in a reasonable way.  But I am constantly creating views that turn complex queries into arrays, which I can then further query, nest, and unnest as possible.

I’m just about done with this series; next time, I’ll show how I can combine arrays and CTEs to perform the sorts of operations that I described above, without having to pollute my database namespace with lots of unnecessary views.