在PostgreSQL中移动

在PostgreSQL中移动

本文介绍了在PostgreSQL中移动(更新)唯一列值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

使用MS SQL Server,以下工作正常:

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

但是,使用PostgreSQL,以下操作失败:

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 ?

谢谢!

编辑:如果您想知道为什么这样做(至少对我来说),这是一个更完整的方案。

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

此表包含以下数据:

category1 : (foo, bar), (foo2, bar2)
category2 : (foo4, bar4), (foo3, bar3)

请注意,类别2中的(foo4,bar4)在(foo3,bar3)之前。
现在,如果要对一个类别中的项目重新排序,我需要更新category_position ...但是由于PK,我无法像使用SQL Server一样使用PostgreSQL来移位值。

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.

推荐答案

这确实有点令人困惑,因为在DML操作期间,所有其他约束都是在语句级别上评估的,只有PK /唯一约束是在每行级别上评估的。

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.

真正的问题是:为什么要对主键值执行此操作? PK值毫无意义,因此似乎没有必要增加所有值(无论使用哪种DBMS)

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中移动(更新)唯一列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-27 21:00