1

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.

4
  • I tested your GROUP BY query on PostgreSQL 11 and I got array of array as you want {{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. Commented Mar 7, 2019 at 11:22
  • It has to do with the version of the server on which it runs. The code will not work on postgres 9.2. Commented Mar 7, 2019 at 11:24
  • I strongly suggest to move your tasks to the latest PG 11 - we just did (abandoned all previous 9.5, 9.6 and 10 versions) and advantages are absolutely amazing. Commented Mar 7, 2019 at 11:27
  • I just moved to a higher version and everything works fine now. Commented Mar 7, 2019 at 11:40

1 Answer 1

1

I just moved to a higher postgres version and everything works fine now.

Sign up to request clarification or add additional context in comments.

1 Comment

Postgres 9.2 is not longer supported anyway

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.