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 AS
 'SELECT CASE WHEN array_position($1,$2) IS NULL THEN $1 || $2 ELSE $1 END;'

And use it like this:

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

But a function is inherently inferior to adding a WHERE condition. The query at the top only writes a new row version if the column value actually changes. See:

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

Answer 2

I had the same problem and this is how I solved.

const user = User.findOne({where: {id}})
User.update({topics: (user.topics.indexOf('topicId') > -1) ? user.topics : Sequelize.fn('array_append', Sequelize.col('topics'), topicId) }, {where: {id}})

Though it definitely feels like there is a better way.