主页 / dba / 问题 / 301806
Accepted
albttx
Asked: 2021-10-29 02:03:23 +0800 CST

PostgreSQL：按顺序插入并增加插入上方的所有值（为插入留出“空间”）[重复]

  • 772

我有一张桌子，需要将我的物品存储在特定位置，用户可以“移动”物品的位置以为新物品“腾出空间”。

这是桌子的样子

CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,
    context    VARCHAR(128) NOT NULL,
    position   INTEGER      NOT NULL,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    UNIQUE (context, position)
);

INSERT INTO keys (key_name, context, position)
VALUES
('A.1', 'ctx_A', 0), 
('A.2', 'ctx_A', 1), 
('A.3', 'ctx_A', 2), 
('A.4', 'ctx_A', 3),
('B.1', 'ctx_B', 0), 
('B.2', 'ctx_B', 1), 
('B.3', 'ctx_B', 2), 
('B.4', 'ctx_B', 3);

我希望能够在位置 1 插入一个键或移动position一个 ( UNIQUE) 键，并且position对于每个大于 new 的值，它都会自动将整数增加 1 INSERT。

这是我到目前为止尝试过的

UPDATE keys
SET position = position + 1
WHERE context = 'ctx_A' AND position >= 2;

错误：重复的键值违反了唯一约束“keys_context_position_key”详细信息：键（上下文，“位置”）=（ctx_A，3）已经存在。

但它不起作用。

编辑

我正在使用 Docker 映像postgres:12.7

我发现使用UNIQUE (context, position) DEFERRABLE允许我执行

UPDATE keys SET position = position + 1 WHERE context = 'ctx_A' AND position > 1;

但是有点像

BEGIN;
  UPDATE keys SET position=2 WHERE context='ctx_A' AND key_name='A.4';
  UPDATE keys SET position=3 WHERE context='ctx_A' AND key_name='A.3';
COMMIT;

仍然无法正常工作！

postgresql unique-constraint

2 个回答

  1. Best Answer

    首先，我将按要求回答问题，然后根据我的看法提出一些改进建议，然后建议您完全修改您的架构！下面的所有代码都可以在这里找到。我还为没有PRIMARY KEY更新的“简化”框架添加了一个新的单独小提琴 - 它使用GENERATED列作为可能的简化策略 - 请参见此处。

    问的问题：

    CREATE TABLE   keys 
(
  key_name   VARCHAR(128),

  context    VARCHAR(128) NOT NULL,

  pos   INTEGER      NOT NULL,  -- changed to "pos" - position is a keyword
                                  -- https://www.postgresql.org/docs/12/sql-keywords-appendix.html
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

  marker TEXT NOT NULL,

  CONSTRAINT keys_pk PRIMARY KEY (key_name)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ctxt_pos_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);

    有几点需要注意：

    • 我将名为“position”的列更改为“pos”，因为“position”这个词是 PostgreSQL 的关键字。

    • 我在一个名为“marker”的列中放入了一个虚拟列，这样我就可以跟踪我的修改——当然，你可以删除它——但它有助于有一个易于识别的测试字段。

    然后我按如下方式填充它：

    INSERT INTO keys (key_name, context, pos, marker)
VALUES
('A.1', 'ctx_A', 0, 'marker 1 initial'), 
('A.2', 'ctx_A', 1, 'marker 2 initial'), 
('A.3', 'ctx_A', 2, 'marker 3 initial'), 
('A.4', 'ctx_A', 3, 'marker 4 initial'),
('B.1', 'ctx_B', 0, 'marker 5 initial'), 
('B.2', 'ctx_B', 1, 'marker 6 initial'), 
('B.3', 'ctx_B', 2, 'marker 7 initial'), 
('B.4', 'ctx_B', 3, 'marker 8 initial');

    然后，我跑了：

    BEGIN TRANSACTION;
  UPDATE keys
    SET 
      key_name = 
        LEFT(key_name, STRPOS(key_name, '.')) || ((SPLIT_PART(key_name, '.', 2)::INT + 1))::TEXT,
      context  = 'ctx_A',
      pos = pos + 1
    WHERE LEFT(key_name, STRPOS(key_name, '.')) = 'A.';
  INSERT INTO keys (key_name, context, pos, marker)
    VALUES ('A.1', 'ctx_A_new', 1, 'marker_new');
COMMIT;

    然后运行（检查）SELECT * FROM keys ORDER BY key_name;。

    结果：

    key_name      context   pos                    created_at   marker
     A.1    ctx_A_new   1   2021-10-28 14:00:46.929092+01   marker_new
     A.2    ctx_A       1   2021-10-28 14:00:46.916923+01   marker 1 initial
     A.3    ctx_A       2   2021-10-28 14:00:46.916923+01   marker 2 initial
     A.4    ctx_A       3   2021-10-28 14:00:46.916923+01   marker 3 initial
     A.5    ctx_A       4   2021-10-28 14:00:46.916923+01   marker 4 initial
     B.1    ctx_B       0   2021-10-28 14:00:46.916923+01   marker 5 initial
     B.2    ctx_B       1   2021-10-28 14:00:46.916923+01   marker 6 initial
     B.3    ctx_B       2   2021-10-28 14:00:46.916923+01   marker 7 initial
     B.4    ctx_B       3   2021-10-28 14:00:46.916923+01   marker 8 initial

    因此，新记录就在那里，我们已经“提升”了INTEGER部分key_name-PRIMARY KEY即已修改并保留了顺序。

    您也可以这样做 - 更灵活，因为 SQL 做得不好的一件事是字符串操作 - 字符串（也许“曾经”会更好......）被认为是“原子的”并将它们拆分并重新组合在一起不是 SQL 的强项！话虽如此，近年来，正则表达式库等已经有了很大的改进。

    因此，我们可以，例如：

    CREATE TABLE   keys_bis 
(
  key_alpha TEXT         NOT NULL, 

  key_num   INTEGER      NOT NULL,
  
  context    VARCHAR(128) NOT NULL,

  pos   INTEGER      NOT NULL,

  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  
  marker TEXT NOT NULL,  -- this is for tracing purposes

  -- It's up to you to ensure that the key_name field is valid!

  CONSTRAINT keys_bis_pk PRIMARY KEY (key_alpha, key_num)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ctxt_pos_bis_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);

    并填充它：

    INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
VALUES
('A.', 1, 'ctx_A', 0, 'marker 1 initial'), 
('A.', 2, 'ctx_A', 1, 'marker 2 initial'), 
('A.', 3, 'ctx_A', 2, 'marker 3 initial'), 
('A.', 4, 'ctx_A', 3, 'marker 4 initial'),
('B.', 1, 'ctx_B', 0, 'marker 5 initial'), 
('B.', 2, 'ctx_B', 1, 'marker 6 initial'), 
('B.', 3, 'ctx_B', 2, 'marker 7 initial'), 
('B.', 4, 'ctx_B', 3, 'marker 8 initial');

    并检查SELECT * FROM keys_bis;- 结果：

    key_alpha   key_num context pos                   created_at        marker
       A.         1   ctx_A   0 2021-10-28 14:31:04.126172+01   marker 1 initial
       A.         2   ctx_A   1 2021-10-28 14:31:04.126172+01   marker 2 initial
       A.         3   ctx_A   2 2021-10-28 14:31:04.126172+01   marker 3 initial
...
... snipped for brevity
...

    然后我们运行更新：

    BEGIN TRANSACTION;
  UPDATE keys_bis 
    SET 
      key_num = key_num + 1,
      context = 'ctx_A',
      pos   = pos + 1
    WHERE key_alpha = 'A.';
  INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
  VALUES ('A.', 1, 'ctx_A_new', 1, 'marker_new');
COMMIT;

    这成功运行 - 我们检查 - SELECT * FROM keys_bis ORDER BY key_alpha, key_num;- 结果：

    key_alpha   key_num   context   pos                    created_at       marker
       A.         1 ctx_A_new   1   2021-10-28 14:42:39.161205+01   marker_new
       A.         2 ctx_A       1   2021-10-28 14:42:39.148996+01   marker 1 initial
       A.         3 ctx_A       2   2021-10-28 14:42:39.148996+01   marker 2 initial
       A.         4 ctx_A       3   2021-10-28 14:42:39.148996+01   marker 3 initial
       A.         5 ctx_A       4   2021-10-28 14:42:39.148996+01   marker 4 initial
       B.         1 ctx_B       0   2021-10-28 14:42:39.148996+01   marker 5 initial
       B.         2 ctx_B       1   2021-10-28 14:42:39.148996+01   marker 6 initial
       B.         3 ctx_B       2   2021-10-28 14:42:39.148996+01   marker 7 initial
       B.         4 ctx_B       3   2021-10-28 14:42:39.148996+01   marker 8 initial

    因此，我们可以看到 的INTEGER部分PRIMARY KEY已经增加并“向上” - 例如，我们可以看到，"marker 4 initial"现在5作为 . 的数字部分PRIMARY KEY。

    架构更改：

    你真的需要修改你的模式 - 放入一个INTEGER代理键。更新 a 的一部分PRIMARY KEY有一种“不好的代码味道” ——你可以在更新PK. 我强烈建议即使更容易，这也不是您应该定期做的事情-如果它是与另一个系统的一次性集成，很好，但是日常-您需要搜索另一个解决方案！PKUPDATE

    我知道您可能处于I have to interface with a legacy system或的情况I'm only the consultant and not allowed to make changes...。

    你为什么不使用context和pos作为PK？两者都已经是NOT NULL并且两者都在一起UNIQUE- 因此是 a 的好候选人PK！

    如果您想/可以更改表格，这里有几段代码可能会有所帮助。您可以使用以下部分/全部功能创建一个新列UPDATE或创建一个INSERT新表（从这里查看小提琴底部）：

    --
-- A few ideas from https://stackoverflow.com/a/51193857/470530
--

WITH test AS
(
  SELECT 'AASDFSD.435434' AS v
  UNION ALL SELECT 'SDXVZ.343534'
  UNION ALL SELECT 'AEE#3434$%$^%^&^&^&^&AAL.12345'
)
SELECT
  v,
  RIGHT(v, LENGTH(v) - STRPOS(v, '.')),
  SUBSTRING(v FROM '^[A-Z]+[\.]'),  -- will pick AA., BBB., CCSXDEER., 
  SUBSTRING(v FROM '\d+$'),
  (select (regexp_matches(v, '[^A,]+', 'g'))[1] offset 0 limit 1) as c1
FROM
  test;

    结果：

                 v  right   substring   substring   c1
AASDFSD.435434  435434  AASDFSD.    435434  SDFSD.435434
SDXVZ.343534    343534  SDXVZ.  343534  SDXVZ.343534
AEE#3434$%$^%^&^&^&^&AAL.12345  12345       12345   EE#3434$%$^%^&^&^&^&

    如果可能的话，你最好避免使用正则表达式——它们是资源密集型的！

    为什么这是一场噩梦！

    要在表格中间执行 an UPDATE，您必须手动跟踪所有前面的 s，以便知道toUPDATE的哪些部分- 如下所示：PKUPDATE

    BEGIN TRANSACTION;
  UPDATE keys_bis 
    SET 
      key_num = key_num + 1,
      context = 'ctx_A',
      pos   = pos + 1
    WHERE (key_alpha = 'A.' AND pos >= 2);
  INSERT INTO keys_bis (key_alpha, key_num, context, pos, marker)
  VALUES ('A.', 3, 'ctx_A_new_3', 3, 'marker_new_3');
COMMIT;

    请参阅小提琴的结果（底部）。

    只要您记得设置，它将起作用：

    WHERE (key_alpha = 'A.' AND pos >= 2);

    和

    VALUES ('A.', 3, 'ctx_A_new_3', 3, 'marker_new_3');

    子句中的pos值比子句中UPDATE的新值小1 ！因此，一个很好的函数候选者？您在自己的答案中有一个功能，但您在这里手动执行此操作：posWHERE

     WHERE key_name IN ('A.3', 'A.4');

    如果你要使用一个函数，那么至少在函数的参数中设置它。如果您有 500 个值（或 5,000 或...）会怎样？

    简化：

    我为这部分做了一个单独的小提琴 - 见这里。

    I reread the question and noticed that it wasn't in fact the PK that required UPDATEing - it was the UNIQUE constraint - the principles however are very similar. For the purposes of a complete explanation of how I would go about this, and making use of an extremely useful feature of all of the major RDBMSs - that is GENERATED fields/columns! GENERATED is the SQL Standard name.

    These also go by the name COMPUTED, CALCULATED, PERSISTED, DERIVED or even VIRTUAL - although the VIRTUAL (best avoided) keyword is also (confusingly) used for the storage class of the column (STORED or VIRTUAL) - the first meaning that the value is actually stored on disk and the second meaning that it's calculated on the fly. As of the time of writing (28/10/2021), PostgreSQL doesn't support VIRTUAL columns.

    I created a new table as follows:

    CREATE TABLE   keys_ter 
(
  key_name   VARCHAR(128),
  context    VARCHAR(128) NOT NULL,
  pos   INTEGER      NOT NULL,  
  created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
  marker TEXT NOT NULL,

  key_alpha TEXT GENERATED ALWAYS AS (LEFT(key_name, STRPOS(key_name, '.'))) STORED,

  key_num   INT  GENERATED ALWAYS AS (SPLIT_PART(key_name, '.', 2)::INT) STORED,

  CONSTRAINT keys_ter_pk PRIMARY KEY (key_name)
    DEFERRABLE INITIALLY IMMEDIATE,

  CONSTRAINT ter_ctxt_pos_uq UNIQUE(context, pos)
    DEFERRABLE INITIALLY IMMEDIATE
);

    Populate as for the original keys table - then SELECT * FROM keys_ter ORDER BY key_alpha, key_num; - result:

    key_name    context pos                   created_at    marker  key_alpha    
    key_num
     A.1      ctx_A   0 2021-10-28 17:02:17.48598+01    marker 1 initial    A.  1
     A.2      ctx_A   1 2021-10-28 17:02:17.48598+01    marker 2 initial    A.  2
     A.3      ctx_A   2 2021-10-28 17:02:17.48598+01    marker 3 initial    A.  3
...
... snipped for brevity
...

    We can see the GENERATED fields - this makes things slightly easier when UPDATING if we happen to know the INTEGER part of the PK as follows:

    BEGIN TRANSACTION;  -- INSERTING new record between pos at 2 new record - pos = 2
  UPDATE keys_ter
    SET 
      pos = pos + 1
  WHERE key_num > 2 AND context = 'ctx_A';              -- we know the key_num = 2!
  INSERT INTO keys_ter (key_name, context, pos, marker)
    VALUES('A.43', 'ctx_A', 2, 'marker_new_A.43');
COMMIT;

    Update successful - we check SELECT * FROM keys_ter ORDER BY context, pos; - result:

        key_name    context pos                   created_at    marker   
   key_alpha    key_num
         A.1      ctx_A   0 2021-10-28 17:02:17.48598+01    marker 1 initial    A.  1
         A.2      ctx_A   1 2021-10-28 17:02:17.48598+01    marker 2 initial    A.  2
        A.43      ctx_A   2 2021-10-28 17:02:17.499004+01   marker_new_A.43 A.  43
         A.3      ctx_A   3 2021-10-28 17:02:17.48598+01    marker 3 initial    A.  3
         A.4      ctx_A   4 2021-10-28 17:02:17.48598+01    marker 4 initial    A.  4
         B.1      ctx_B   0 2021-10-28 17:02:17.48598+01    marker 5 initial    B.  1
    ...
    ... snipped for brevity
    ...

    这可能是“方便”的，并且在某些情况下让生活更轻松 - 只是一个想法。À+ et +1 pour un question intéressante qui m'a fait réflechir！

    • 3

  2. 我找到了解决方案

    这是一个 sql 脚本，显示它是如何工作的

    DROP TABLE IF EXISTS keys;

CREATE TABLE   keys (
    key_name   VARCHAR(128) UNIQUE NOT NULL PRIMARY KEY,

    context    VARCHAR(128) NOT NULL,

    position   INTEGER      NOT NULL,

    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW(),

    UNIQUE (context, position) DEFERRABLE
);

CREATE OR REPLACE FUNCTION glide_keys_position()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE keys
    SET position = position + 1
    WHERE
        context = NEW.context AND position >= NEW.position;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_glide_keys_position
BEFORE INSERT ON keys
FOR EACH ROW EXECUTE PROCEDURE glide_keys_position();


-- here a sample of data
INSERT INTO keys (key_name, context, position)
VALUES
('A.1', 'ctx_A', 0), 
('A.2', 'ctx_A', 1), 
('A.3', 'ctx_A', 2), 
('A.4', 'ctx_A', 3),
('B.1', 'ctx_B', 0), 
('B.2', 'ctx_B', 1), 
('B.3', 'ctx_B', 2), 
('B.4', 'ctx_B', 3);

-- UPDATE keys SET position = position + 1 WHERE context = 'ctx_A' AND position > 1;

INSERT INTO keys (key_name, context, position) VALUES ('A.10', 'ctx_A', 2);

BEGIN;
  SET CONSTRAINTS keys_context_position_key DEFERRED;

  UPDATE keys
  SET position = CASE key_name
    WHEN 'A.4' THEN (SELECT position FROM keys WHERE context='ctx_A' AND key_name = 'A.3')
    WHEN 'A.3' THEN (SELECT position FROM keys WHERE context='ctx_A' AND key_name = 'A.4')
  END
  WHERE key_name IN ('A.3', 'A.4');
COMMIT;

SELECT * FROM keys WHERE context='ctx_A' ORDER BY position;

    输出：

    DROP TABLE
CREATE TABLE
CREATE FUNCTION
CREATE TRIGGER
INSERT 0 8
INSERT 0 1
BEGIN
SET CONSTRAINTS
UPDATE 2
COMMIT
 key_name | context | position |          created_at
----------+---------+----------+-------------------------------
 A.1      | ctx_A   |        0 | 2021-10-28 11:09:44.667153+00
 A.2      | ctx_A   |        1 | 2021-10-28 11:09:44.667153+00
 A.10     | ctx_A   |        2 | 2021-10-28 11:09:44.669278+00
 A.4      | ctx_A   |        3 | 2021-10-28 11:09:44.667153+00
 A.3      | ctx_A   |        4 | 2021-10-28 11:09:44.667153+00
(5 rows)

    ps：我把它放在一个公共的github gist

    • 1

相关问题