在 PostgreSQL 中壓縮序列

我id serial PRIMARY KEY在 PostgreSQL 表中有一個列。id由於我刪除了相應的行，因此缺少許多s。

現在我想通過重新啟動序列並以保留id原始順序的方式重新分配 s來“壓縮”表。id可能嗎？

例子：

• 現在：

id | data  
----+-------
 1 | hello
 2 | world
 4 | foo
 5 | bar

• 後：

id | data  
----+-------
 1 | hello
 2 | world
 3 | foo
 4 | bar

我嘗試了 StackOverflow 答案中的建議，但沒有奏效：

# alter sequence t_id_seq restart;
ALTER SEQUENCE
# update t set id=default;
ERROR:  duplicate key value violates unique constraint t_pkey
DETAIL:  Key (id)=(1) already exists.

首先，序列中的間隙是可以預料的。問問自己是否真的需要刪除它們。如果你只是忍受它，你的生活會變得更簡單。為了獲得無間隙的數字，（通常更好的）替代方法是使用 a VIEWwith row_number()。此相關答案中的範例：

• 涉及具有多個表的多個事務的無間隙序列

以下是一些消除差距的方法。

1.新的，原始的桌子

避免了獨特的違規和表膨脹的並發症，並且速度很快。僅適用於不受 FK 引用、表上的視圖或其他依賴對像或並發訪問約束的***簡單情況。*一次交易即可避免意外：

BEGIN;
LOCK tbl; -- optionally: IN SHARE MODE to allow concurrent reads

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL);

INSERT INTO tbl_new -- no target list in this case
SELECT row_number() OVER (ORDER BY id), data  -- all columns in default order
FROM   tbl;

ALTER SEQUENCE tbl_id_seq OWNED BY tbl_new.id;  -- make new table own sequence

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL)複製結構，包括。來自原始表的約束和預設值。然後使新表列擁有序列：

• PostgreSQL 中的 Activerecord-import 和串列列

並將其重置為新的最大值：

• 不同步時如何重置 Postgres 的主鍵序列？

這樣做的好處是新表不會膨脹並且聚集在id.

2.UPDATE到位

這會產生很多死行，並且VACUUM稍後需要（自動）。

如果該serial列也是**PRIMARY KEY（如您的情況）或具有UNIQUE約束，則必須避免該過程中的唯一違規行為**。PK / UNIQUE 約束的（更便宜的）預設值是NOT DEFERRABLE，它強制在每一行之後進行檢查。此相關問題下關於 SO 的所有詳細資訊：

• 約束定義 DEFERRABLE INITIALLY IMMEDIATE 仍然是 DEFERRED？

您可以將約束定義為DEFERRABLE（這使得它更昂貴）。

或者您可以刪除約束並在完成後將其添加回來：

BEGIN;

LOCK tbl;

ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;  -- remove PK

UPDATE tbl t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back

COMMIT;

當您有引用列的約束時，兩者都不可能，FOREIGN KEY因為（根據文件）：

被引用的列必須是被引用表中不可延遲的唯一或主鍵約束的列。

您需要（鎖定所有涉及的表並）刪除/重新創建 FK 約束並手動更新所有 FK 值（請參閱選項3。）。或者，您必須在一秒鐘內將值移開UPDATE以避免衝突。例如，假設您沒有負數：

BEGIN;
LOCK tbl;

UPDATE tbl SET id = id * -1;  -- avoid conflicts

UPDATE tbl t
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id DESC) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

缺點如前所述。

3. 臨時表, TRUNCATE,INSERT

---

如果您有足夠的 RAM，則另一種選擇。這結合了前兩種方式的一些優點。幾乎與選項1一樣快。您將獲得一個沒有膨脹的原始新表，但保留所有約束和依賴項，就像選項2中一樣。

但是，根據文件：

TRUNCATE 不能用於具有 來自其他表的外鍵引用的表，除非所有此類表也在同一命令中被截斷。在這種情況下檢查有效性將需要表掃描，而重點不是這樣做。

大膽強調我的。

您可以暫時刪除 FK 約束並使用數據修改 CTE 更新所有 FK 列：

SET temp_buffers = 500MB;   -- example value, see 1st link below

BEGIN;

CREATE TEMP TABLE tbl_tmp AS
SELECT row_number() OVER (ORDER BY id) AS new_id, *
FROM   tbl
ORDER  BY id;  -- order here to use index (if one exists)

-- drop FK constraints in other tables referencing this one
-- which takes out an exclusive lock on those tables

TRUNCATE tbl;

INSERT INTO tbl
SELECT new_id, data  -- list all columns in order
FROM tbl_tmp;        -- rely on established order in tbl_tmp
-- ORDER BY id;      -- only to be absolutely sure (not necessary)

--  example for table "fk_tbl" with FK column "fk_id"
UPDATE fk_tbl f
SET    fk_id = t.new_id  -- set to new ID
FROM   tbl_tmp t
WHERE  f.fk_id = t.id;   -- match on old ID

-- add FK constraints in other tables back

COMMIT;

相關，更多細節：

• 如何刪除重複條目？
• 如何在 SQL 中高效地“合併”兩張表？

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