CODEWITHSUNDEEP
sqlpostgresql
Kufu
Kufu

Reputation: 59

Postgresql merge columns

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}

Upvotes: 0

Views: 519

Answers (2)

Stefanov.sm
Stefanov.sm

Reputation: 13029

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.

Upvotes: 2

Pooya
Pooya

Reputation: 3173

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;

Upvotes: 2

Related Questions