Add array element if it's not contained already

Question

I am trying to add integer to an array if does not exist in the array, yet. But the following creates duplicate values:

User.update(
{
    'topics': sequelize.fn('array_append', sequelize.col('topics'), topicId),
},
{where: {uuid: id}})

Is there an equivalent function in PostgreSQL/sequelize to MongoDB $addToSet?

Answer 1

Quoting the MongoDB Manual:

The $addToSet operator adds a value to an array unless the value is already present, in which case $addToSet does nothing to that array.

This SQL command does what you ask:

UPDATE "user"
SET    topics = topics || topicId
WHERE  uuid = id
AND    NOT (topics @> ARRAY[topicId]);

@> being the array contains operator and || array-to-element concatenation in this case. Details in the manual here.

Related:

• Check if value exists in Postgres array

Does not work for null values. In this case consider: array_position(topics, topicId) IS NULL. See:

• Check if NULL exists in Postgres array

You could wrap this into a simple function:

CREATE OR REPLACE FUNCTION f_array_append_uniq (anyarray, anyelement)
  RETURNS anyarray
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
 'SELECT CASE WHEN array_position($1,$2) IS NULL THEN $1 || $2 ELSE $1 END;'

And use it like:

...
SET    topics = f_array_append_uniq (topics, topicId)
...

But the query at the top only even writes a new row version if the column value actually changes. To achieve the same (without function):

UPDATE "user"
SET    topics = topics || topicId
WHERE  uuid = id
AND    array_position(topics,topicId) IS NOT NULL;

The last one being my favorite.

See:

• How do I (or can I) SELECT DISTINCT on multiple columns?