I am trying to update a text array column :
var data = [];
for (const tag of tags) {
var tmp = {'rids': [rid], 'id': tag.id, 'uid' : uid};
data.push(tmp);
}
const condition = pgp.as.format(' WHERE CAST(v.uid AS INTEGER) = t.uid and v.id = t.id');
const insertQuery = pgp.helpers.update(data, ['?id', '?uid', 'rids'], 'table_tags') + condition + ' ' + 'RETURNING t.tag';
return db.any(insertQuery);
This works but it replaces the columns value.
How to keep the current column value and append the new ones ?
Like this : {somevalue, someothervalue, newinsertedvalue}
Instead of : {newinsertedvalue}
This is the query i used to use in a php drupal project :
db_query("UPDATE table_tags set rids = rids || (:rid) WHERE uid = :uid and id = :id", array(':rid' => '{'.$rid.'}', ':uid' => $uid, ':id' => $tag_id));
Your value-concatenation logic is a special case, not supported by the default update
. You will have to write the query statically, with the dynamic part - values, generated via helpers.values function.
const values = helpers.values(data, ['id', 'uid', 'rids']);
const query = `UPDATE table_tags AS t SET rids = t.rids || v.rids FROM
(VALUES${values}) as v(id, uid, rids)
WHERE CAST(v.uid AS INTEGER) = t.uid AND v.id = t.id RETURNING t.tag`.