postgresql 将数据选入 Postgres 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11762398/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting data into a Postgres array
提问by harman_kardon
I have the following data:
我有以下数据：
name id url
John 1 someurl.com
Matt 2 cool.com
Sam 3 stackoverflow.com
How can I write an SQL statement in Postgres to select this data into a multi-dimensional array, i.e.:
如何在 Postgres 中编写 SQL 语句将这些数据选择到多维数组中，即：
{{John, 1, someurl.com}, {Matt, 2, cool.com}, {Sam, 3, stackoverflow.com}}
I've seen this kind of array usage before in Postgres but have no idea how to select data from a table into this array format.
我之前在 Postgres 中见过这种数组用法，但不知道如何从表中选择数据到这种数组格式。
Assuming here that all the columns are of type text.
假设这里所有的列都是类型text。
回答by Erwin Brandstetter
You cannot use array_agg()to produce multi-dimensional arrays, at least not up to PostgreSQL 9.4.
(But the upcoming Postgres 9.5ships a new variant of array_agg()that can!)
您不能用于array_agg()生成多维数组，至少在 PostgreSQL 9.4 之前不能。
（但即将发布的Postgres 9.5提供了该罐的新变体array_agg()！）
What you get out of @Matt Ball's queryis an array of records (the_table[]).
您从@Matt Ball 的查询中得到的是一组记录 ( the_table[])。
An array can only hold elements of the same base type. You obviously have number and string types. Convert all columns (that aren't already) to textto make it work.
一个数组只能包含相同基本类型的元素。你显然有数字和字符串类型。将所有列（还没有）转换text为使其工作。
You can create an aggregate function for this like I demonstrated to you herebefore.
您可以为此创建一个聚合函数，就像我之前在这里向您演示的那样。
CREATE AGGREGATE array_agg_mult (anyarray) (
SFUNC = array_cat
,STYPE = anyarray
,INITCOND = '{}'
);
Call:
称呼：
SELECT array_agg_mult(ARRAY[ARRAY[name, id::text, url]]) AS tbl_mult_arr
FROM tbl;
Note the additional ARRAY[]layer to make it a multidimensional array (2-dimenstional, to be precise).
注意附加ARRAY[]层使其成为多维数组（准确地说是二维）。
Instant demo:
即时演示：
WITH tbl(id, txt) AS (
VALUES
(1::int, 'foo'::text)
,(2, 'bar')
,(3, '}b",') -- txt has meta-characters
)
, x AS (
SELECT array_agg_mult(ARRAY[ARRAY[id::text,txt]]) AS t
FROM tbl
)
SELECT *, t[1][3] AS arr_element_1_1, t[3][4] AS arr_element_3_2
FROM x;
回答by Matt Ball
You need to use an aggregate function; array_aggshould do what you need.
您需要使用聚合函数；array_agg应该做你需要的。
SELECT array_agg(s) FROM (SELECT name, id, url FROM the_table ORDER BY id) AS s;
