postgres: concat string with array

Question

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.

@ErwinBrandstetter they already exist in db, and store settings, that parsed by app, i want to replace existing string with a new one that constructed from algorithm described in question. — helios
– helios, Commented Jul 22, 2016 at 13:38
Do you need double quotes in the new string, too? What do possible actual strings look like? Can they have a double quote inside the string? Are they always upper case? Put all the defining information into the question, and please always declare your version of Postgres. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 22, 2016 at 13:40
@ErwinBrandstetter can you please explain, how it can be applied to update statement? — helios
– helios, Commented Jul 23, 2016 at 4:08

Laurenz Albe · Accepted Answer · 2016-07-22 13:33:35Z

1

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)

answered Jul 22, 2016 at 13:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

Erwin Brandstetter · Accepted Answer · 2016-07-22 13:34:03Z

1

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.

answered Jul 22, 2016 at 13:34

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

postgres: concat string with array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related