Postgres always surprises me with its robustness and cutting edge developments and extensions. postgis was years ahead of the competition. I’ve recently discovered that PostgreSQL offers the rather sweet feature of aggregating multiple rows into an array, and then the implode-like feature of array_to_string to complete the coolness.
The problem for me was simply finding it again. I’d made a number of searches, including:
- postgres rows to columns
- postgres rows to columns array_to_string
- postgres rows to columns separator
- postgres rows to aggregate
- postgres rows to group
- postgres rows to array
The last one was the key which lead me to stack overflow, and was just what I was looking for.
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
Once I’ve created that function, I can now do this to turn my rows into a comma separated list:
SELECT array_to_string(array_accum(engine_name), ', ')
FROM search_engine
which turns this:
Bing
Google
into this:
Bing, Google