Postgresql merge columns-CodePudding

I have a main table which is like this

CREATE TABLE IF NOT EXISTS people 
(
    country text,
    full_name text,
    last_name text,
    children_names text[]
);

INSERT INTO people (country, full_name, last_name, children_names) 
VALUES ('de', 'han', 'post', '{"joe", "joe1", "joe2", "joe3"}');

I can merge full_name and last_name like this

SELECT
    full_name || '_' || last_name AS obid
FROM 
    people;

but I couldn't do the same thing with the children_names column I want to select children like this

select children_names 
from people;

but I want to add last_name filed at the end of each child like this

{joe_han,joe1_han,joe2_han,joe3_han}

CodePudding user response：

Using a scalar subquery:

SELECT
  full_name||'_'||last_name obid, 
  (select array_agg(u||'_'||full_name) from unnest(children_names) u) children
FROM people;

Yet it would be much better to structure your data as @BarbarosÖzhan suggests.

CodePudding user response：

You should use unnest function to extract data and then add last_name column. After merging data you have to use aggregate to create an array.

Demo

SELECT
    full_name || '_' || last_name as obid,
    array_agg(value || '_' || last_name)
FROM people CROSS JOIN unnest(children_names) value
GROUP BY 1;