Updating values in PostgreSQL array

Question

I have some columns in PostgreSQL database that are array. I want to add a new value (in UPDATE) in it if the value don't exists, otherwise, don't add anytihing. I don't want to overwrite the current value of the array, but only add the element to it.

Is possible do this in a query or I need to do this inside a function? I'm using PostgreSQL.

Answer 1

This should be as simple as this example for an integer array (integer[]):

UPDATE tbl SET col = col || 5
WHERE  (5 = ANY(col)) IS NOT TRUE;

A WHERE clause like:

WHERE  5 <> ALL(col)

would also catch the case of an empty array '{}'::int[], but fail if a NULL value appears as element of the array.

If your arrays never contain NULL as element, consider actual array operators, possibly supported by a GIN index.

UPDATE tbl SET col = col || 5
WHERE  NOT col @> '{5}';

See:

• Check if value exists in Postgres array
• Can PostgreSQL index array columns?