Merging array and table in PostgreSQL

Question

I have a table with all the ids in an array like format and another table that matches the ids with the names. I'm required to write a query that creates creates a new table with 2 columns : ids (which is an array of ids) and names (an array with the corresponding names of the ids).

INPUT TABLES :

main

ids
---------
i1 | i2
i2 | i3
i3

agents

ids    names
--------------
i1     agent1
i2     agent2
i3     agent3

OUTPUT :

ids        names
----------------------------
(i1, i2)   (agent1, agent2)
(i2, i3)   (agent2, agent3)
(i3)       (agent3)

Output can also be in this format : i1 | i2 for all the elements, that would be fine as well, not absolutely necessary that they should be converted to arrays as I've done below.

I managed to convert the | separated ids column to arrays, but couldn't progress after that.

string_to_array(substr(translate(translate(
  main.ids, '|', ','), ' ', ''), 1, length(main.ids) - 3), ',') 
  AS ids

IVO GELOV · Accepted Answer · 2021-07-03 08:12:00Z

1

Use the unnest function to expand the array into rows, then join with the other table and finally use array_agg to aggregate rows into single value:

SELECT 
'( ' || main.ids || ' )' AS ids,
'( ' || array_to_string(
  (SELECT array_agg(agents.names) 
    FROM unnest(
      string_to_array( replace(main.ids,' ',''), '|')
    ) as tmp(temp_main_ids) 
    LEFT JOIN agents ON tmp.temp_main_ids = agents.ids
  ),' | ') 
|| ' )' AS names
FROM main

edited Jul 3, 2021 at 8:12

answered Jun 29, 2021 at 17:17

IVO GELOV

14.3k1 gold badge21 silver badges30 bronze badges

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

2 Comments

Suraj Over a year ago

Hi, thanks a lot for your reply. Can you add the table name with the parameters, like usemain_ids and agent_ids for these columns. This was like a sample example I created and I'm finding it hard to replace this.

IVO GELOV Over a year ago

I am not sure that I follow you - but I prefixed the columns with their respective table name.

Collectives™ on Stack Overflow

Merging array and table in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related