Today, I was asked how to select each element in an array as a separate row. In other words, I needed to convert an array into rows (one for each array element). Below is the right way to do it by using the built-in unnest() function (requires PostgreSQL 8.4+):

SELECT UNNEST(ARRAY['Julio','César','de','León']) AS names

If you don’t have PostgreSQL 8.4+ available to you, the following solution which was inspired by this page will produce the same results (requires PostgreSQL 8.0+):

SELECT arr[i]
  SELECT generate_series(1, array_upper(arr, 1)) AS i, arr
  FROM (SELECT ARRAY['Julio','César','de','León'] arr) t
) t

Personally, if you will need to split (or explode) an array into rows, it is better to create a quick function that would do this for you. Again, I don’t claim to be a PostgreSQL guru, therefore if you know of a built-in way of doing this in a version of PostgreSQL before 8.4, please let me know.

Categories: BlogSQL


Tomasz Bazan · June 12, 2014 at 6:23 AM

Thank you very much for this. I must replace function unnest. With your help I did. 😉

Jim Keenan · June 25, 2015 at 5:27 PM

Your illustration of SELECT UNNEST(ARRAY[…]) proved very helpful to me on a current project.

Leave a Reply

Your email address will not be published. Required fields are marked *