Postgresql merge columns

Question

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}

better to hold the children names row-wise manner within another table along with creating foreign-primary key relationship among this current table in order to obey normalisation rules for the sake of the DB design. — Barbaros Özhan
– Barbaros Özhan, Commented Dec 27, 2021 at 12:19

Stefanov.sm · Accepted Answer · 2021-12-27 12:56:00Z

2

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.

edited Dec 27, 2021 at 12:56

answered Dec 27, 2021 at 12:43

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

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

Comments

Pooya · Accepted Answer · 2021-12-27 12:17:12Z

2

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;

answered Dec 27, 2021 at 12:17

Pooya

3,2032 gold badges16 silver badges21 bronze badges

1 Comment

404 Over a year ago

This may be faster than a join, I can't test as I don't have a pg system right now: SELECT ARRAY(SELECT cn || '_' || last_name FROM UNNEST(children_names) cn) FROM people;

Collectives™ on Stack Overflow

Postgresql merge columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related