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?
Quoting the MongoDB Manual:
The $addToSet operator adds a value to an array unless the value is already present, in which case
$addToSetdoes 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:
Does not work for null values. In this case consider: array_position(topics, topicId) IS NULL. See:
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:
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.
