Qstacker Qstacker - 1 year ago 81
SQL Question

Updating PostgreSQL array column - avoiding duplicate values

I have an

column and I want to update this column, but avoid having duplicate entries in the array.

Example: First
is the value.
Next time I want to add
. Here
works fine. But it shouldn't allow adding

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download