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.

There is chance that arr_str may contains duplicate values ? — Oto Shavadze
– Oto Shavadze, Commented Apr 26, 2017 at 8:49
arr_str can hold duplicate values but I dont want to add duplicate values — Prata
– Prata, Commented Apr 26, 2017 at 8:57
if arr_str holds {'a', 'b', 'b'} and you want add {'b','c','d'}, what will expected array from this ? — Oto Shavadze
– Oto Shavadze, Commented Apr 26, 2017 at 9:02

Joseph King · Accepted Answer · 2023-12-22 15:01:52Z

28

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 arr_str is null or 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

edited Dec 22, 2023 at 15:01

Joseph King

5,2371 gold badge33 silver badges40 bronze badges

answered Apr 26, 2017 at 15:05

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

3 Comments

Prata Over a year ago

Thank you very much. This is the perfect reply/answer I was looking for. :)

Shubham Gupta Over a year ago

Does not append to rows with arr_str as NULL

pozs Over a year ago

@ShubhamGupta no operations append anything to NULLs. That's kind of the point, as NULL is not an empty array, but rather an unknown array. If you use it for empty arrays, you'll have to use coalesce(arr_str, '{}') in WHEREs and the right hand side of SETs manually.

Daniel Abdelsamed · Accepted Answer · 2018-07-10 09:23:19Z

20

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

edited Jul 10, 2018 at 9:23

Daniel Abdelsamed

4982 silver badges11 bronze badges

answered Apr 26, 2017 at 8:43

Dmitry Chirkin

1,08410 silver badges23 bronze badges

1 Comment

Dagrooms Over a year ago

Great explanation, this helped me get back on track!

Vao Tsun · Accepted Answer · 2017-04-26 08:36:43Z

2

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

answered Apr 26, 2017 at 8:36

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

2 Comments

Prata Over a year ago

Yes, but I actually wanted to UPDATE the column itself. I dont want to SELECT the column and create a new array for updating before doing UPDATE to the column. I only want to use UPDATE query.

Vao Tsun Over a year ago

You should have mentioned that in the question. Please update

Piotr Rogowski · Accepted Answer · 2017-04-26 08:38:06Z

0

try this code:

select array(select distinct unnest(string_to_array('a,b',',') || string_to_array('b,c,d',',')) order by 1)

answered Apr 26, 2017 at 8:38

Piotr Rogowski

3,90021 silver badges24 bronze badges

1 Comment

Prata Over a year ago

Yes, but I actually wanted to UPDATE the column itself. I dont want to SELECT the column and create a new array for updating before doing UPDATE to the column. I only want to use UPDATE query.

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related