PostgreSQL:按順序插入並增加插入上方的所有值(為插入留出“空間”)
我有一張桌子,需要將我的物品儲存在特定位置,使用者可以“移動”物品的位置以為新物品“騰出空間”。
這是桌子的樣子
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 的值,它都會自動將整數增加 1INSERT
。這是我到目前為止嘗試過的
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