PostgreSQL query to return results as a comma separated list
SqlPostgresqlSql Problem Overview
Let say you have a SELECT id from table query (the real case is a complex query) that does return you several results.
The problem is how to get all id return in a single row, comma separated?
Sql Solutions
Solution 1 - Sql
SELECT string_agg(id::text, ',') FROM table
Requires PostgreSQL 9.0 but that's not a problem.
Solution 2 - Sql
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
Solution 3 - Sql
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
Solution 4 - Sql
use array_to_string() & array() function for the same.
select array_to_string(array(select column_name from table_name where id=5), ', ');
Solution 5 - Sql
Use this below query it will work and gives the exact result.
SELECT array_to_string(array_agg(id), ',') FROM table
Output : {1,2,3,4,5}
Solution 6 - Sql
SELECT array_agg(id, ',') FROM table
> {1,2,3,4}
I am using Postgres 11 and EntityFramework is fetching it as array of integers.