PostgreSQL查询以逗号分隔的列表形式返回结果

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

假设您有一个从表查询中选择的id(实际情况是一个复杂的查询)，它会返回几个结果。

The problem is how to get all id return in a single row, comma separated?

问题是如何在一行中获得所有id返回，逗号分隔?

3 个解决方案

#1

104

SELECT string_agg(id, ',') FROM table

从表中选择string_agg(id， '，')

Requires PostgreSQL 9.0 but that's not a problem.

需要PostgreSQL 9.0，但这不是问题。

#2

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}

可以将数组()和array_to_string()函数与查询结合使用。使用SELECT array(从表中选择id);您将得到如下结果:{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

然后，如果您希望删除{}符号，您可以使用array_to_string()函数并使用逗号作为分隔符，因此:选择array_to_string(数组(从表中选择id)， '，'，')将得到如下结果:1、2、3、4、5、6

#3

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

您可以使用psql从任何SQL查询生成CSV:

$ 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.

结果myfile。csv将有SQL resultset列名为csv列标头，查询元组为csv行。

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

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

#1

104