strong textI have the following problem: Given the two tables contacts and organisations :
WITH contacts(oe_id, name, email, person_id) AS (VALUES
(1, 'Mark', '[email protected]', 19650728),
(2, 'Tom', '[email protected]', 20010627),
(1, 'Frank', '[email protected]', 20040709),
(3, 'Petra', '[email protected]', 19700317),
(3, 'Paul', '[email protected]', 19681006)),
organisations(oe_id, name) AS (VALUES
(1, 'Cardiology'),
(2, 'Neurology'),
(3, 'Dermatology'),
(4, 'Churgery'))
I want to get a table with 3 columns: the organisation name, the organisation id, and an array of contactpersons for that organisation. Every array element is also an array with the data of the contact person.
First I created a table in which all contact columns are being aggregated into an array. One array per tuple row:
WITH contacts(oe_id, name, email, person_id) AS (VALUES
(1, 'Mark', '[email protected]', 19650728),
(2, 'Tom', '[email protected]', 20010627),
(1, 'Frank', '[email protected]', 20040709),
(3, 'Petra', '[email protected]', 19700317),
(3, 'Paul', '[email protected]', 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;
This result into:
oe_id | cdata
-------+---------------------------------------
1 | {Mark,[email protected],19650728}
2 | {Tom,[email protected],20010627}
1 | {Frank,[email protected],20040709}
3 | {Petra,[email protected],19700317}
3 | {Paul,[email protected],19681006}
(5 rows)
Next step is to aggregate cdata (contact data) for each organisation id:
WITH contacts(oe_id, name, email, person_id) AS (VALUES
(1, 'Mark', '[email protected]', 19650728),
(2, 'Tom', '[email protected]', 20010627),
(1, 'Frank', '[email protected]', 20040709),
(3, 'Petra', '[email protected]', 19700317),
(3, 'Paul', '[email protected]', 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;
This results into the following:
oe_id | contacts
-------+------------------------------------------------------------------------------------
1 | {"{Mark,[email protected],19650728}","{Frank,[email protected],20040709}"}
2 | {"{Tom,[email protected],20010627}"}
3 | {"{Petra,[email protected],19700317}","{Paul,[email protected],19681006}"}
(3 rows)
As you can see the result is an array. But its elements should also be an array. Instead of an array I get an imploded array as a string.
Wat I want is something like this:
oe_id | contacts
-------+------------------------------------------------------------------------------------
1 | {{Mark,[email protected],19650728},{Frank,[email protected],20040709}}
2 | {{Tom,[email protected],20010627}}
3 | {{Petra,[email protected],19700317},{Paul,[email protected],19681006}}
(3 rows)
If I remove the cast to text array_agg(contacts_aggregated.cdata::text I get:
could not find array type for data type text[]
What am I forgetting/doing wrong? Postgres: psql (9.2.24) and psql (9.6.10, server 9.2.24)
If I run the code using a postgres client 9.6 on a postgres 9.6 server everyting works fine.
{{Mark,[email protected],19650728},{Frank,[email protected],20040709}}- both with "::text" cast and without it. So looks like your problem is really PG version specific. - JosMac