英文:
DELETE with ANY clause not working as expected
问题
我正在尝试使用ANY
子句从我的Postgress数据库中删除特定的行。
这是表的结构 -
---- TABLE A -----
-------------------
rId UUID NOT NULL,
oId UUID NOT NULL,
eId UUID NOT NULL,
--- <other fields>
PRIMARY KEY (rId, oId, eId),
CONSTRAINT fk_r FOREIGN KEY (rId) REFERENCES another_table(rId),
CONSTRAINT fk_eo FOREIGN KEY (oId, eId) REFERENCES some_other_table (oId, eId)
假设这个表有以下行 -
| rId | oId | eId |
-----------------------
| 1 | abc | pqr |
| 1 | abc | xyz |
| 1 | abc | utd |
我想删除这个表中所有满足rId = 1
,oId = abc
和[eId != pqr
和eid != xyz
]的行。
我编写了以下查询 -
DELETE FROM TABLE_A
WHERE rId = 1
AND oId = abc
AND eId != ANY (array['pqr'::uuid, 'xyz'::uuid]);
问题是这个查询不起作用。具有eId = pqr
或eId = xyz
的两行都被删除了。我该如何纠正这个问题?
逐个传递它们不是一个选项(我得到了一个eId
的slice
,我将其作为参数传递给运行此查询的go函数)。
类似下面这样 -
func queryToDelete(req someReq, eIds ...string) *pgx.Batch {
batch := &pgx.Batch{}
deleteQuery := `
DELETE
FROM table_a
WHERE rId = $1
AND oId = $2
AND eId != ANY($3);
`
batch.Queue(deleteQuery, req.rId, req.oId, eIds)
return batch
}
请忽略这个示例中的1
,abc
,pqr
不是UUID。我为了简洁起见保持了这种方式。
英文:
I'm trying to DELETE certain rows in my Postgress DB using the ANY
clause.
Here's the structure -
---- TABLE A -----
-------------------
rId UUID NOT NULL,
oId UUID NOT NULL,
eId UUID NOT NULL,
--- <other fields>
PRIMARY KEY (rId, oId, eId),
CONSTRAINT fk_r FOREIGN KEY (rId) REFERENCES another_table(rId),
CONSTRAINT fk_eo FOREIGN KEY (oId, eId) REFERENCES some_other_table (oId, eId)
Suppose this table has below rows -
| rId | oId | eId |
-----------------------
| 1 | abc | pqr |
| 1 | abc | xyz |
| 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 -
DELETE FROM TABLE_A
WHERE rId = 1
AND oId = abc
AND eId != ANY (array['pqr'::uuid, 'xyz'::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 -
func queryToDelete(req someReq, eIds ...string) *pgx.Batch {
batch := &pgx.Batch{}
deleteQuery := `
DELETE
FROM table_a
WHERE rId = $1
AND oId = $2
AND eId != ANY($3);
`
batch.Queue(deleteQuery, req.rId, req.oId, eIds)
return batch
}
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)
.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论