DELETE语句中的ANY子句不按预期工作。

huangapple go评论123阅读模式
英文:

DELETE with ANY clause not working as expected

问题

我正在尝试使用ANY子句从我的Postgress数据库中删除特定的行。

这是表的结构 -

  1. ---- TABLE A -----
  2. -------------------
  3. rId UUID NOT NULL,
  4. oId UUID NOT NULL,
  5. eId UUID NOT NULL,
  6. --- <other fields>
  7. PRIMARY KEY (rId, oId, eId),
  8. CONSTRAINT fk_r FOREIGN KEY (rId) REFERENCES another_table(rId),
  9. CONSTRAINT fk_eo FOREIGN KEY (oId, eId) REFERENCES some_other_table (oId, eId)

假设这个表有以下行 -

  1. | rId | oId | eId |
  2. -----------------------
  3. | 1 | abc | pqr |
  4. | 1 | abc | xyz |
  5. | 1 | abc | utd |

我想删除这个表中所有满足rId = 1oId = abc和[eId != pqreid != xyz]的行。

我编写了以下查询 -

  1. DELETE FROM TABLE_A
  2. WHERE rId = 1
  3. AND oId = abc
  4. AND eId != ANY (array['pqr'::uuid, 'xyz'::uuid]);

问题是这个查询不起作用。具有eId = pqreId = xyz的两行都被删除了。我该如何纠正这个问题?

逐个传递它们不是一个选项(我得到了一个eIdslice,我将其作为参数传递给运行此查询的go函数)。

类似下面这样 -

  1. func queryToDelete(req someReq, eIds ...string) *pgx.Batch {
  2. batch := &pgx.Batch{}
  3. deleteQuery := `
  4. DELETE
  5. FROM table_a
  6. WHERE rId = $1
  7. AND oId = $2
  8. AND eId != ANY($3);
  9. `
  10. batch.Queue(deleteQuery, req.rId, req.oId, eIds)
  11. return batch
  12. }

请忽略这个示例中的1abcpqr不是UUID。我为了简洁起见保持了这种方式。

英文:

I'm trying to DELETE certain rows in my Postgress DB using the ANY clause.

Here's the structure -

  1. ---- TABLE A -----
  2. -------------------
  3. rId UUID NOT NULL,
  4. oId UUID NOT NULL,
  5. eId UUID NOT NULL,
  6. --- &lt;other fields&gt;
  7. PRIMARY KEY (rId, oId, eId),
  8. CONSTRAINT fk_r FOREIGN KEY (rId) REFERENCES another_table(rId),
  9. CONSTRAINT fk_eo FOREIGN KEY (oId, eId) REFERENCES some_other_table (oId, eId)

Suppose this table has below rows -

  1. | rId | oId | eId |
  2. -----------------------
  3. | 1 | abc | pqr |
  4. | 1 | abc | xyz |
  5. | 1 | abc | utd |

I want to delete all rows from this table that have rId = 1, oId = abc AND [eId != pqr AND eid != xyz]

I write the below query -

  1. DELETE FROM TABLE_A
  2. WHERE rId = 1
  3. AND oId = abc
  4. AND eId != ANY (array[&#39;pqr&#39;::uuid, &#39;xyz&#39;::uuid]);

The problem is that this is not working. Both the rows that have eId = pqr Or eId = xyz are getting deleted. How can I correct this?

Passing them one by one isn't an option (I'm getting a slice of eId which I'm passing as a param to the go function that runs this query).

Something like below -

  1. func queryToDelete(req someReq, eIds ...string) *pgx.Batch {
  2. batch := &amp;pgx.Batch{}
  3. deleteQuery := `
  4. DELETE
  5. FROM table_a
  6. WHERE rId = $1
  7. AND oId = $2
  8. AND eId != ANY($3);
  9. `
  10. batch.Queue(deleteQuery, req.rId, req.oId, eIds)
  11. return batch
  12. }

Please disregard that 1, abc, pqr are not UUIDs in this example. I kept it that way for brevity.

答案1

得分: 1

正如The Impaler在他们的评论中指出的那样,如果$3数组是一个包含多个元素的集合,那么eId != ANY ($3)将始终评估为true

对于你的用例,你应该使用ALL,例如eId != ALL ($3)

英文:

As The Impaler points out in their comment, eId != ANY ($3) will always evaluate to true if the $3 array is a set with more than one element.

For your use case you should instead use ALL, e.g. eId != ALL ($3).

huangapple
  • 本文由 发表于 2021年9月3日 20:51:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/69045283.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定