PostgreSQL query to return results as a comma separated list

Question

Let's say you have a SELECT id from table query (the real case is a complex query) that returns several results.

The problem is how to return all id in one line, separated by comma?

Answer 1

SELECT string_agg(id, ',') FROM table

PostgreSQL 9.0 is required, but this is not a problem.

Answer 2

You can use the array () and array_to_string () functions associated with your request. Using a SELECT array( SELECT id FROM table ); You will get the result, for example: {1,2,3,4,5,6}

Then, if you want to remove the {} characters, you can simply use the array_to_string () function and use a comma as a separator, so: SELECT array_to_string( array( SELECT id FROM table ), ',' ) will get a result like: 1,2,3 4,5,6

Answer 3

You can generate CSV from any SQL query using psql:

 $ psql > \o myfile.csv > \f ',' > \a > SELECT col1 AS column1, col2 AS column2 ... FROM ... 

As a result, myfile.csv will have the SQL result set column names as CSV column headers, and query tuples as CSV strings.

h / t http://pookey.co.uk/wordpress/archives/51-outputting-from-postgres-to-csv