postgresql - Postgres 查询应该返回一个数组的数组而不是一个字符串数组

Question

strong text我有以下问题:给定两个表联系人和组织:

WITH contacts(oe_id, name, email, person_id) AS (VALUES
    (1, 'Mark', 'm.smith@test.nl', 19650728),
    (2, 'Tom', 't.b.smith@test.nl', 20010627), 
    (1, 'Frank', 'f.j.smith@test.nl', 20040709), 
    (3, 'Petra', 'p.ringenaldus@test.nl', 19700317), 
    (3, 'Paul', 'p.m.sprengers@test.nl', 19681006)),
organisations(oe_id, name) AS (VALUES
    (1, 'Cardiology'), 
    (2, 'Neurology'), 
    (3, 'Dermatology'), 
    (4, 'Churgery'))

我想要一个包含 3 列的表:组织名称、组织 ID 和该组织的一组联系人。每个数组元素也是一个包含联系人数据的数组。

首先，我创建了一个表，其中所有联系人列都被聚合到一个数组中。每个元组行一个数组:

 WITH contacts(oe_id, name, email, person_id) AS (VALUES
    (1, 'Mark', 'm.smith@test.nl', 19650728), 
    (2, 'Tom', 't.b.smith@test.nl', 20010627), 
    (1, 'Frank', 'f.j.smith@test.nl', 20040709), 
    (3, 'Petra', 'p.ringenaldus@test.nl', 19700317), 
    (3, 'Paul', 'p.m.sprengers@test.nl', 19681006)),
organisations(oe_id, name) AS (VALUES
    (1, 'Cardiology'), 
    (2, 'Neurology'), 
    (3, 'Dermatology'), 
    (4, 'Churgery')),
contacts_aggregated(oe_id, cdata) AS (
    select oe_id, ARRAY[name, email, person_id::text] from contacts) 
select * from contacts_aggregated;

这个结果变成:

 oe_id |                 cdata
-------+---------------------------------------
     1 | {Mark,m.smith@test.nl,19650728}
     2 | {Tom,t.b.smith@test.nl,20010627}
     1 | {Frank,f.j.smith@test.nl,20040709}
     3 | {Petra,p.ringenaldus@test.nl,19700317}
     3 | {Paul,p.m.sprengers@test.nl,19681006}
(5 rows)

下一步是聚合每个组织 ID 的 cdata(联系人数据):

WITH contacts(oe_id, name, email, person_id) AS (VALUES
    (1, 'Mark', 'm.smith@test.nl', 19650728), 
    (2, 'Tom', 't.b.smith@test.nl', 20010627), 
    (1, 'Frank', 'f.j.smith@test.nl', 20040709), 
    (3, 'Petra', 'p.ringenaldus@test.nl', 19700317), 
    (3, 'Paul', 'p.m.sprengers@test.nl', 19681006)),
organisations(oe_id, name) AS (VALUES
    (1, 'Cardiology'), 
    (2, 'Neurology'), 
    (3, 'Dermatology'), 
    (4, 'Churgery')),
contacts_aggregated(oe_id, cdata) AS (
    select oe_id, ARRAY[name, email, person_id::text] from contacts),
contacts_for_organisations(oe_id, contacts) AS (
    SELECT organisations.oe_id, array_agg(contacts_aggregated.cdata::text) 
    FROM organisations
    JOIN contacts_aggregated USING(oe_id) 
    GROUP BY oe_id)
SELECT * FROM contacts_for_organisations;

结果如下:

 oe_id |                                      contacts
-------+------------------------------------------------------------------------------------
     1 | {"{Mark,m.smith@test.nl,19650728}","{Frank,f.j.smith@test.nl,20040709}"}
     2 | {"{Tom,t.b.smith@test.nl,20010627}"}
     3 | {"{Petra,p.ringenaldus@test.nl,19700317}","{Paul,p.m.sprengers@test.nl,19681006}"}
(3 rows)

如您所见，结果是一个数组。但它的元素也应该是一个数组。我得到的不是数组，而是一个字符串形式的内爆数组。

我想要的是这样的:

 oe_id |                                      contacts
-------+------------------------------------------------------------------------------------
     1 | {{Mark,m.smith@test.nl,19650728},{Frank,f.j.smith@test.nl,20040709}}
     2 | {{Tom,t.b.smith@test.nl,20010627}}
     3 | {{Petra,p.ringenaldus@test.nl,19700317},{Paul,p.m.sprengers@test.nl,19681006}}
(3 rows)

如果我删除对文本 array_agg(contacts_aggregated.cdata::text 我得到:

could not find array type for data type text[]

我忘记/做错了什么？Postgres: psql (9.2.24) 和 psql (9.6.10, server 9.2.24)

如果我在 postgres 9.6 服务器上使用 postgres 客户端 9.6 运行代码，一切正常。

Answer 1

我刚刚升级到更高版本的 postgres，现在一切正常。