问题描述
使用 MS SQL Server,以下工作正常:
CREATE TABLE #temptable(mykey int primary key)插入 #temptable 值 (1)插入 #temptable 值 (2)更新#temptable SET mykey=mykey+1
但是,使用 PostgreSQL,以下失败:
CREATE TABLE pg_temp.tbl_test(testkey 整数主键)插入 pg_temp.tbl_test 值 (1)插入 pg_temp.tbl_test 值 (2)更新 pg_temp.tbl_test 设置 testkey=testkey+1
错误:重复键值违反唯一约束tbl_test_pkey"详细信息:密钥 (testkey)=(2) 已存在.
我需要增加一个表中一列的每个值,这是复合唯一约束的一部分.我怎样才能在一个语句中做到这一点?
谢谢!
如果您想知道为什么这是有道理的(至少对我而言),这里有一个更完整的场景.
我有一张按类别组织的项目表.每个项目在类别中都有特定的位置.
category_id (PK) |类别位置 (PK) |item_attribute_1 |item_attribute_21 |1 |富 |酒吧1 |2 |foo2 |酒吧22 |1 |foo4 |酒吧42 |2 |foo3 |酒吧3
此表包含如下数据:
category1 : (foo, bar), (foo2, bar2)类别2:(foo4,bar4),(foo3,bar3)
请注意, (foo4, bar4) 在 category2 中位于 (foo3, bar3) 之前.现在,如果我想重新排序一个类别中的项目,我需要更新 category_position...但是由于 PK,我不能像使用 SQL Server 那样使用 PostgreSQL 移动值.
这确实有点令人困惑,因为所有其他约束都是在语句级别上评估的,在 DML 操作期间,只有 PK/唯一约束在每行级别上评估.
但您可以通过将主键约束声明为可延迟来解决此问题:
创建表 tbl_test(测试键整数,约束 pk_tbl_test 主键 (testkey) 最初可延迟 立即);插入 tbl_test 值 (1), (2);设置所有延迟的约束;更新 tbl_test设置 testkey = testkey +1;
延迟约束确实有一些开销,因此通过将其定义为initially immediate
,可以将这个开销保持在最低限度.您可以在需要时使用 set constraint
推迟约束评估.
然而,真正的问题是:为什么需要对主键值执行此操作?PK 值没有任何意义,因此似乎没有必要增加所有值(无论使用何种 DBMS)
Using MS SQL Server, the following works fine:
CREATE TABLE #temptable(mykey int primary key)
INSERT INTO #temptable VALUES (1)
INSERT INTO #temptable VALUES (2)
UPDATE #temptable SET mykey=mykey+1
However, using PostgreSQL, the following fails:
CREATE TABLE pg_temp.tbl_test(testkey integer primary key)
INSERT INTO pg_temp.tbl_test VALUES (1)
INSERT INTO pg_temp.tbl_test VALUES (2)
UPDATE pg_temp.tbl_test SET testkey=testkey+1
I need to increment every value of one column in one table, which is part of a composite unique constraint. How can I do this in one statement ?
Thanks !
Edit: If you are wondering why this makes sense (at least to me), here is a more complete scenario.
I have one table of items organized in categories. Each item has a particular position in the category.
category_id (PK) | category_position (PK) | item_attribute_1 | item_attribute_2
1 | 1 | foo | bar
1 | 2 | foo2 | bar2
2 | 1 | foo4 | bar4
2 | 2 | foo3 | bar3
This table contains data like:
category1 : (foo, bar), (foo2, bar2)
category2 : (foo4, bar4), (foo3, bar3)
Note that (foo4, bar4) comes before (foo3, bar3) in category2.Now if I want to reorder items in one category, I need to update category_position... But because of the PK, I cannot shift values using PostgreSQL as I could with SQL Server.
This is indeed a bit confusing as all other constraints are evaluated on a statement level, only PK/unique constraint are evaluated on a per row level during DML operations.
But you can work around that by declaring the primary key constraint as deferrable:
create table tbl_test
(
testkey INTEGER,
constraint pk_tbl_test primary key (testkey) deferrable initially immediate
);
insert into tbl_test values (1), (2);
set constraints all deferred;
update tbl_test
set testkey = testkey +1;
Deferred constraints do have some overhead, so by defining it as initially immediate
this overhead is kept to a minimum. You can the defer the constraint evaluation when you need it by using set constraint
.
The real question however is: why would you need to do this on a primary key value? The PK values has no meaning whatsoever, so it seems rather unnecessary to increment all values (regardless of the DBMS being used)
这篇关于在 PostgreSQL 中移动(更新)唯一列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!