选择数据到Postgres数组中

一尘不染

选择数据到Postgres数组中

sql

我有以下数据：

name          id             url

John          1              someurl.com
Matt          2              cool.com
Sam           3              codingdict.com

如何在Postgres中编写SQL语句以选择此数据为多维数组，即：

{{John, 1, someurl.com}, {Matt, 2, cool.com}, {Sam, 3, stackoverflow.com}}

我以前在Postgres中已经见过这种数组的用法，但是不知道如何从表中选择数据到这种数组格式。

假设这里所有列都是type text。

阅读 200

2021-03-10

共1个答案

一尘不染

您不能用于array_agg()生成多维数组，至少不能用于PostgreSQL 9.4或更高版本。
（但是即将发布的Postgres 9.5附带了该罐的新变体array_agg()！）

您从@Matt Ball的查询中得到的是一个记录数组（the_table[]）。

数组只能包含相同基本类型的元素。您显然具有数字和字符串类型。转换所有（尚未存在的）列text以使其起作用。

您可以为此创建一个聚合函数，就像我之前在这里向您展示的那样。

CREATE AGGREGATE array_agg_mult (anyarray)  (
    SFUNC     = array_cat
   ,STYPE     = anyarray
   ,INITCOND  = '{}'
);

称呼：

SELECT array_agg_mult(ARRAY[ARRAY[name, id::text, url]]) AS tbl_mult_arr
FROM   tbl;

注意附加ARRAY[]层使其成为多维数组（精确地说是二维的）。

即时演示：

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;

2021-03-10