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