PostgreSQL – Split Array To Multiple Rows

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]
FROM (
  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.