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?
6 Answers
SELECT string_agg(id::text, ',') FROM table
Requires PostgreSQL 9.0 but that's not a problem.
5 Comments
JZC
Note that for me at least, string_agg didn't like taking an int for its first argument so I did something like:
string_agg(CAST(id as varchar), ',') instead.
Alphaaa
@JZC, or even easier:
string_agg(id::text, ',')
M A Hossain Tonu
If you wanted to sort the column
select string_agg(id, ', ' order by id desc) from table
ChristoKiwi
I ran into duplicates with my query but solved it with
STRING_AGG(DISTINCT customer_name, ',')
java-addict301
for id as uuid only @JZC cast solution worked
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
1 Comment
Alex R.
SELECT array_to_string( id, ',' ) AS id FROM tableYou 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
Comments
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}
1 Comment
Community
Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
use array_to_string() & array() function for the same.
select array_to_string(array(select column_name from table_name where id=5), ', ');
SELECT array_agg(id, ',') FROM table
{1,2,3,4}
I am using Postgres 11 and EntityFramework is fetching it as array of integers.
array_agg()function in particular.