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

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 tabl1set    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 tabl1set    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

With default pg install you can merge two arrays with || operator:

select arr_str || '{a, b}' from tabl1

But in that case you will get duplicates.

To avoid them, you can unnest array into rowset and distinct it:

select ARRAY(SELECT DISTINCT UNNEST(arr_str || '{a,b,c}')) from tabl1

If your values are integers, there is more elegant way to get uniq array values with intarray contrib module and uniq() function:https://www.postgresql.org/docs/current/static/intarray.html

You can add these integer array functions by using:

CREATE EXTENSION intarray

you can concatenate arrays and then aggregate distinct values:

t=# with a as (select unnest('{a, b}'::text[] || '{b,c,d}'::text[]) a) select array_agg(distinct a) from a; array_agg----------- {a,b,c,d}(1 row)Time: 1.312 ms