Updating PostgreSQL array column - avoiding duplicate values

Question

I have an integer[] column and I want to update this column, but avoid having duplicate entries in the array.

Example: First [123] is the value. Next time I want to add 234. Here array_append() works fine. But it shouldn't allow adding 123 again.

So my question: How can I append a value to an array only if that item is not present yet in the array.

Craig Ringer · Accepted Answer · 2014-04-03 02:01:13Z

4

For integer arrays, you can use the intarray extension's uniq function:

CREATE EXTENSION intarray;

UPDATE thetable SET thecol = uniq(array_append(thecol, 32)) WHERE ...

answered Apr 3, 2014 at 2:01

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

Comments

pozs · Accepted Answer · 2014-04-02 09:50:39Z

1

Pseudo code:

UPDATE "my_table"
   SET "int_array" = array_append("int_array", :element_to_insert)
 WHERE :some_filters
   AND :element_to_insert <> ALL ("int_array")

More possibilities:

http://www.postgresql.org/docs/9.3/static/functions-array.html http://www.postgresql.org/docs/9.3/static/functions-comparisons.html

answered Apr 2, 2014 at 9:50

pozs

36.6k5 gold badges61 silver badges66 bronze badges

2 Comments

Qstacker Over a year ago

Dosnt this flush and update only new entries every time? Does it keep the old entries?

pozs Over a year ago

This updates only, when the array doesn't contain the element you want to append. array contains element = element = ANY (array) - array does not contain element = element <> ALL (array)

Collectives™ on Stack Overflow

Updating PostgreSQL array column - avoiding duplicate values

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related