Postgresql

PostgreSQL：按順序插入並增加插入上方的所有值（為插入留出“空間”）

  • October 28, 2021

我有一張桌子，需要將我的物品儲存在特定位置，使用者可以“移動”物品的位置以為新物品“騰出空間”。

這是桌子的樣子

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;

仍然無法正常工作！

首先，我將按要求回答問題，然後根據我的看法提出一些改進建議，然後建議您完全修改您的架構！下面的所有程式碼都可以在這裡找到。我還為沒有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. 我強烈建議，即使更容易，這也不是你應該定期做的事情——如果它是與另一個系統的一次性集成，很好，但是日常——你需要搜尋另一個解決方案！PK``UPDATE

我知道您可能處於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的哪些部分- 如下所示：PK``UPDATE

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 ！因此，一個很好的函式候選者？您在自己的答案中有一個功能，但您在這里手動執行此操作：pos``WHERE

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

如果你要使用一個函式，那麼至少在函式的參數中設置它。如果您有 500 個值（或 5,000 或…）會怎樣？

簡化：

我為這部分做了一個單獨的小提琴 - 見這裡。

我重新閱讀了這個問題，並註意到它實際上並不是PK需要的UPDATE——它是UNIQUE約束——但是原則非常相似。為了完整解釋我將如何進行此操作，並利用所有主要 RDBMS 的一個非常有用的功能 - 即GENERATED欄位/列！GENERATED是SQL 標準名稱。

它們的名稱也有COMPUTED, CALCULATED, PERSISTED,DERIVED甚至VIRTUAL——儘管VIRTUAL（最好避免的）關鍵字也（令人困惑地）用於列的儲存類（STORED或VIRTUAL）——第一個意思是該值實際上儲存在磁碟上，第二個意思是這意味著它是即時計算的。截至撰寫本文時（28/10/2021），PostgreSQL不支持 VIRTUAL列。

我創建了一個新表，如下所示：

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
);

填充原始鍵表 - 然後SELECT * FROM keys_ter ORDER BY key_alpha, key_num;- 結果：

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
...

我們可以看到欄位 -如果我們碰巧知道以下部分，GENERATED這會使事情變得稍微容易一些：UPDATING``INTEGER``PK

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;

更新成功——我們檢查SELECT * FROM keys_ter ORDER BY context, pos;——結果：

   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
   ...

這可能是“方便”的，並且在某些情況下讓生活更輕鬆 - 只是一個想法。À+ 和 +1 表示一個讓我思考的有趣問題！

我找到了解決方案

這是一個 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

引用自：https://dba.stackexchange.com/questions/301806

相關問答

Postgresql

僅當 Y 值相同時才允許多行具有相同的 X 值？

  • September 16, 2022
檢視問答
Postgresql

了解 pg_wal 和歸檔

  • September 13, 2022
檢視問答
Postgresql

pg_dump 將文件轉儲到 ROOT

  • September 13, 2022
檢視問答
Postgresql

pg_start_backup 與 pg_basebackup

  • September 9, 2022
檢視問答
Postgresql

我可以安全刪除哪些 pg_wal 文件？

  • September 9, 2022
檢視問答
Postgresql

如何在一台伺服器上製定多個數據庫的備份策略

  • September 1, 2022
檢視問答