PostgreSQL query to return results as a comma separated list

Question

SELECT string_agg(id::text, ',') FROM table

Requires PostgreSQL 9.0 but that's not a problem.

You can use the array() and array_to_string() functions togetter with your query. With SELECT array( SELECT id FROM table ); you will get a result like: {1,2,3,4,5,6}

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

You can generate a CSV from any SQL query using psql:

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

The resulting myfile.csv will have the SQL resultset column names as CSV column headers, and the query tuples as CSV rows.

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

use array_to_string() & array() function for the same.

select array_to_string(array(select column_name from table_name where id=5), ', ');