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

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

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 = 1oId = abc和[eId != pqreid != xyz]的行。

我编写了以下查询 -

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

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

逐个传递它们不是一个选项(我得到了一个eIdslice,我将其作为参数传递给运行此查询的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
}

请忽略这个示例中的1abcpqr不是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,
--- &lt;other fields&gt;
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[&#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 -

func queryToDelete(req someReq, eIds ...string) *pgx.Batch {
	batch := &amp;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).

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:

确定