Question 90795 in PostgreSQL Question posted in
The official documentation can be found here.

How to combine several String Array columns into one String Array in Postgresql

Suppose that I have a table with the following two columns:

create table contacts (
    first_name varchar[],
    last_name varchar[]
);

And I have the following two rows:

INSERT INTO contacts (first_name, last_name)
VALUES (ARRAY['Samin'] , Array['Var']),
(ARRAY['Sara', 'pooya'] , Array['Rad', 'Mohammadi']);

select * from contacts;

I want to do a query that results in the following output:

#row1: {Samin-Var}
#row2: {Sara-Rad, pooya-Mohammadi}
Tags:

2

Answers


  1. 0

    This is can done using a self-defined function in PostgreSQL.

    CREATE OR REPLACE FUNCTION merge_names(firsts varchar[], lasts varchar[])
RETURNS varchar[] AS
$$
DECLARE m varchar[];
    BEGIN
        FOR i IN 1..cardinality(firsts)
        LOOP
            m[i]:= firsts[i] || '-' || lasts[i];
        END LOOP;
        RETURN m;
    END;
$$ LANGUAGE plpgsql;

    Here we assume that the lengths of firsts and lasts are the same.

    Usage example:

    SELECT merge_names(first_name, last_name)
FROM contacts;
    Login or Signup to reply.
  2. 0

    You can use a lateral join that turns the arrays into rows and aggregates them back into a the names you want:

    select n.names
from contacts c
  cross join lateral (
     select array_agg(concat_ws('-', x.first_name, x.last_name) order by x.nr) as names
     from unnest(c.first_name, c.last_name) with ordinality as x(first_name, last_name, nr)
  ) as n

    Online example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search