CODEWITHSUNDEEP
postgresql
helios
helios

Reputation: 324

postgres: concat string with array

Assume we have a column with text with next structure: ["A", "B", "C"], how to concat it with array ARRAY['A','C','D','E'] and produce string ["A", "B", "C", "D", "E"] (string without repeated elements)?

postgres version is 9.4.8

Column data can be ["A", "B", "C"] or null, how it can be concatenated with ARRAY['A','C','D','E'] (actually it can be a string, but i need to add elements to existing string without repeating them), resulting string must have the following pattern ["A", "B", "C", "D", "E"]

Solved with script, that alternate db via pdo.

Upvotes: 0

Views: 5762

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657932

Transform the string to another array, unnest both and take the ordered UNION of both to form a new array:

SELECT ARRAY(
   SELECT * FROM unnest(ARRAY['A','C','D','E'])
   UNION 
   SELECT * FROM unnest(string_to_array(translate('["A", "B", "C"]', '[]"', ''), ', '))
   ORDER BY 1
   );

I removed the characters []" to proceed with the simple case. You would need to explain why you have them / need them ...

An ARRAY constructor is faster for the simple case.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247235

SELECT array_agg(x) FROM
   (SELECT * FROM unnest(ARRAY['A', 'B', 'C'])
    UNION
    SELECT * FROM unnest(ARRAY['A','C','D','E'])
   ) a(x);
┌─────────────┐
│  array_agg  │
├─────────────┤
│ {D,B,E,C,A} │
└─────────────┘
(1 row)

Upvotes: 1

Related Questions