Postgres append or set each elements(if not exists) of an array to an array column

Question

If I have

select arr_str from tabl1;
-> {'a', 'b'}

then how do I add this {'b','c','d'} array to column arr_str so that I would get the following result

select arr_str from tabl1;
-> {'a', 'b', 'c', 'd'}

I don't want to SELECT the column and create a new array for updating. I only want to use UPDATE query.

Answer 1

I'll assume that arr_str is of type text[] (although you did not use the proper format for them, so I may be wrong; if that's the case, you'll need to cast your value to text[]).

Use the following statement, if you want to remove duplications, which are already present in the arr_str column:

update tabl1
set    arr_str = (select array_agg(distinct e) from unnest(arr_str || '{b,c,d}') e)
where  not arr_str @> '{b,c,d}'

Or, use the following one when you want to preserve existing duplications:

update tabl1
set    arr_str = arr_str || array(select unnest('{b,c,d}'::text[]) except select unnest(arr_str))
where  not arr_str @> '{b,c,d}'

Both of these statements won't touch rows, which won't be affected anyway (look at the where not arr_str @> '{b,c,d}' predicate). This is usualy the best practice, and is almost always recommended, when triggers are involved.

http://rextester.com/GKS7382