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.