Turning a PostgreSQL array to rows

May 24, 2014 . By Reuven

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:

| 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.

Related Posts

Reminder: My “Intro to SQL” course is this Sunday!

Reminder: My “Intro to SQL” course is this Sunday!

Join the data revolution with my “Intro to SQL” course!

Join the data revolution with my “Intro to SQL” course!

Yes, you can master regular expressions!

Yes, you can master regular expressions!
  • The very nice series of articles! Probably, you didn’t notice, but this post should be categorized in PostgreSQL.

  • Developer says:

    With respect to postgres array could you please clarify the following
    How can we do wild card character search in array columns. Like for example. select * from foo where stuff ilike ‘%def’.
    Will it be possible to search

    Foo Table is as follows.
    | id | stuff |
    | 1 | {abc,def,abc} |
    | 2 | {abc,xyz}

    • I’m not sure if you can do this very easily. You would probably have to turn the array into a set of rows, and then apply a regexp or “like” search on the rows you got from the array. There might be a more elegant way to do this, but I can’t think of it offhand. (Maybe someone else here has a good idea?)

    • Arie Yuster says:

      You can do it that way:

      select * from (
      select unnest(array[‘abcd’,’efghcd’,’hcdij’,’cdklmn’,’qwerty’]) as stuff
      ) a where a.stuff like ‘%cd%’;

  • Eoghan Murray says:

    Aha, got it:

    INSERT INTO airport_passengers (name, number) SELECT arr[1], arr[2]::int FROM (SELECT string_to_array(‘London Heathrow, 74985748’, ‘, ‘) as a) x;

  • Eoghan Murray says:

    unnest is handy for making multiple rows, but what if you want to insert an array as a single row?


    CREATE TABLE airport_passengers (name character varying, number integer);

    INSERT INTO airport_passengers VALUES (string_to_array(‘London Heathrow, 74985748’, ‘, ‘));

    This incorrectly inserts the whole array as a string into the “name” column instead of both columns. Any idea how to achieve this?


  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}