英文:
How to increment a column that is part of a constraint?
问题
To avoid the duplicate error caused by the UNIQUE constraint on mytable (username, index), you can update the indexes in two steps:
- First, increment the indexes greater than or equal to 2 for John:
UPDATE mytable
SET index = index + 1
WHERE username = 'john' AND
index >= 2;
- Then, insert the new value for John with index 2:
INSERT INTO mytable (username, index, value)
VALUES ('john', 2, 'new value');
This two-step process should allow you to achieve the desired result without violating the UNIQUE constraint.
英文:
I'm storing a list for each user.
username|index|...
john | 1 |a
john | 2 |b
john | 3 |c
jane | 1 |...
jane | 2 |...
Suppose I were to do an addition for john before his index 2, I have to increment the index so that the new table becomes:
username|index|...
john | 1 |a
john | 2 |new value
john | 3 |b
john | 4 |c
jane | 1 |...
jane | 2 |...
My solution is to first increment john's index that is greater than or equals to 2; and then do an insert statement.
The problem is that this produces a duplicate error:
UPDATE mytable
SET index = index + 1
WHERE username = 'john' AND
index >= 2
This is because I have a UNIQUE constraint on mytable (username, index).
What is the workaround for this?
答案1
得分: 0
你需要创建一个DEFERRABLE唯一约束才能使其生效，因为这样的约束将在语句结束时进行检查。
然而，当添加新行时更新许多行是不明智的。使用double precision列来保持顺序，因为这样你可以随时在旧值之间插入新值。在查询表时使用row_number()为顺序分配整数编号。
英文:
You need to create a DEFERRABLE unique constraint for that to work, because such a constraint will be checked at the end of the statement.
However, it is silly to update many rows when a new one is added. Use a double precision column to persist the order, because then you can always insert new values between old ones. Assign integer numbers to the order using row_number() when you query the table.
答案2
得分: 0
除了视图可能是处理这种类型的索引的最佳选项之外，另一种方法是使用一个子查询，其索引顺序被倒转，以便首先更新较旧的索引。相关的是 FOR UPDATE 子句，该子查询必须位于其底部。
UPDATE tab
SET idx = cte.idx + 1
FROM (SELECT idx
FROM tab
WHERE idx >= 2
ORDER BY idx DESC
FOR UPDATE) cte
WHERE tab.username = 'john'
AND tab.idx = cte.idx;
SELECT * FROM tab
输出：
| username | idx |
|---|---|
| john | 1 |
| jane | 1 |
| jane | 2 |
| john | 4 |
| john | 3 |
在这里查看演示。
英文:
Apart from the fact that a view would be the best option to handle such kind of index, one way to do it is to have a subquery whose order of indices is inverted, so that older indices are updated first. Relevant is the FOR UPDATE clause, that the subquery has to have at the bottom of it.
UPDATE tab
SET idx = cte.idx + 1
FROM (SELECT idx
FROM tab
WHERE idx >= 2
ORDER BY idx DESC
FOR UPDATE) cte
WHERE tab.username = 'john'
AND tab.idx = cte.idx;
SELECT * FROM tab
Output:
| username | idx |
|---|---|
| john | 1 |
| jane | 1 |
| jane | 2 |
| john | 4 |
| john | 3 |
Check the demo here.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论