Pivot Columns Values into a Set #PostgreSQL

Depending on the project you are working on, you may have to generate a set which contains every value found (across several columns) into a set consisting of a single column.

For example you have 3 columns A, B, C:

a1, b1, c1

a2, b2, c2

a3, b3, b3

and you need:

a1

a2

a3

b1

b2

b3

c1

c2

c3

To achieve this you can use the array function called unnest:

select unnest( array[A, B, C] )

from foo

This takes an array of tuples created by array[a,b,c] which looks like this:

{ {a1, b1, c1}, {a2, b2, c2}, {a3, b3, b3} }

and returns a set that contains every value in the array of column values.

Some caveats to consider:

1. The types of the columns must be equivalent or they must be cast
2. The unnest function is available in PostgreSQL 8.4 or greater