how to delete if not exists in postgresql

Question

for (var i in req.body.categories) {
  db.query("DELETE FROM guide_categories WHERE NOT IN($1) AND guide_id = $1",[req.body.categories[i],req.params._id], function(err, data) {
    if (err) console.log(err);
    console.log('updated');
  })                                
}

I am trying to delete if not exists in the array and I am passing the array from postman like categories:["3","2","1"]. How to do this??

score 2 · Answer 1 · answered Jan 02 '18 at 15:52

3 errors stand out:

The column name (category?) in front of IN is missing in the query, making the statement illegal syntax.
You are using the same parameter $1 twice. The second one should probably be $2.
The IN() construct expects a list of values or a subquery, not an array. While you pass an array, use <> ALL() instead.

This query should work:

DELETE FROM guide_categories
WHERE category <> ALL($1)
AND guide_id = $2;

Careful with NULL values, though!
Details:

How to use ANY instead of IN in a WHERE clause with Rails?

score 1 · Accepted Answer · answered Jan 02 '18 at 15:57

1

There are several issues in your code:

don't use a loop over the categories. Doing that, you will delete everything (first time you delete everything but category 3, so you only have category 3 left, then you delete except category 2, so you delete everything).
you're missing a column name before NOT IN.
do not use NOT IN, but <> ALL, and pass the entire array
Fix the second $1.

So it should probably something along the lines of:

  db.query("DELETE FROM guide_categories WHERE category_id <> ALL ($1) AND guide_id = $2",
      [
          req.body.categories,
          req.params._id
      ],
      function(err, data)
  {
    if (err) console.log(err);
    console.log('updated');
  })

answered Jan 02 '18 at 15:57

jcaron

17,302
6
32
46

what is this "<>" symbol represents?? – M gowda Jan 02 '18 at 16:12
Not equal, as in basic SQL. – jcaron Jan 02 '18 at 16:17
will it not delete string values?? – M gowda Jan 05 '18 at 10:07
It will delete data from the `guide_categories` table only, which as far as I understand from what little information you provided is a join table which should only contain the ids of the categories and guides. – jcaron Jan 05 '18 at 10:13
`db.query('DELETE FROM position WHERE position <> ALL ($1) AND user_id=$2 RETURNING *',[data.positionDetails.position,companyuser.rows[0]._id],function(err,data1){ if (err) return callback(new Error(err)); })` here it is deleting all values. – M gowda Jan 05 '18 at 10:20
It is deleting everything you're asking it to delete, i.e. all rows in the `position` table where `position` is not `data.positionDetails[i].position` and `user_id` is `companyuser.rows[0]._id‌`. If that's not what you want, you'll need to open a new question on this topic with all relevant info, as this is completely unrelated to the original question. – jcaron Jan 05 '18 at 10:23

how to delete if not exists in postgresql

2 Answers2