Converting rows (records) to and from arrays in Postgres

Arrays are one of those more special features in PostgreSQL. Like any more esoteric features, you have people both in favor and opposed to them. On the pro side you have the fact that you can have an arbitrary number of elements without wasting space and/or having cumbersome table structure. On the con side you have the fact that this steps outside of the relational realm. Also, you can't easily (ie without writing triggers) enforce foreign key constraints on them.

Like them or dislike them, if you need to use them, here is how to convert rows to array and vice-versa.

First to convert from an array into a rowset, you can use the solution given by merlin:

create or replace function explode_array(in_array anyarray) returns setof anyelement as
$$
    select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

select * from explode_array(array[1,2,3,4]);

The beauty of this solution is that SQL is guaranteed to be present in every database (unlike other possible languages). Also, remember that most of the time you don't need to transform array to rows because PostgreSQL supports arrays directly in the ANY/ALL operators.

To get the reverse (for which credit goes to the guys and gals on the #postgresql IRC channel), do the following:

select array(select id from table)

Watch out that if you get a long array (more than 1000 element for example), pgAdmin won't show it (it will seem as if the returned result would be empty). This is just an interface glitch, but can be misleading.

PS. A little rant/request about the documentation: when you search for postgres related information in search engines, many times outdated versions of the documentation come up at the top, which can be slightly annoying (and also it can give the wrong advice). I suggested on #postgresql to either automatically forward the search-engine crawlers to the latest version of the documentation or have a link at the top saying view this page for the latest release. My idea didn't seem to get any traction, so here are some alternatives:

As was pointed out to me in the discussion current is an alias for the latest release, so the following two URLs are equivalent at the moment (given that 8.3 is the latest release):

http://www.postgresql.org/docs/current/static/functions-comparisons.html

http://www.postgresql.org/docs/8.3/static/functions-comparisons.html

You can use this information in two ways:

First, when you search for the documentation, include inurl:current (if you are using Google) in the search to get the most up-to-date documentation (probably there are similar operators for other search engines as well).

Second, when you are linking to the documentation, use the version with current rather than with a specific version number, so that over time we can direct search engines more towards those URLs (which are updated after every release).

Update: in 8.4 there will be built-in functions to do this. Now if we could get an unsigned int datatype...

Update: If you have a function which takes an array as a parameter and you would like to pass in the result of a query, just wrap it into ARRAY(...) like this: SELECT * FROM count_array(ARRAY(SELECT * FROM test))