Qstacker Qstacker - 2 months ago 14
SQL Question

Updating PostgreSQL array column - avoiding duplicate values

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.

Answer

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

Comments