如何將序列號添加到表中？

我有一個表有超過 1000000 行是從 CSV 文件導入的。該表沒有主鍵，我想在表中添加一個具有遞增 bigint 的主鍵。這是我的表 DDL：

CREATE TABLE public.ecdict (
   word varchar(256) NULL,
   phonetic varchar(512) NULL,
   definition varchar(80000) NULL,
   "translation" varchar(80000) NULL,
   pos varchar(100) NULL,
   collins int4 NULL,
   oxford int4 NULL,
   tag varchar(128) NULL,
   bnc int4 NULL,
   frq int4 NULL,
   exchange varchar(128) NULL,
   detail varchar(2000) NULL,
   audio varchar(8) NULL,
   id int8 NULL
);

該id列是新添加的，所有數據都為 NULL，我嘗試使用此命令：

update ecdict c
set id = c2.seqnum
from (
  select c2.*, row_number() over () as seqnum
  from ecdict c2
) c2
where c2.pkid = c.pkid;

但是我找不到用於連接的表的唯一列，我應該怎麼做才能添加id具有遞增bigint值的列？

像這樣添加id列，該列將自動填充序列號，並索引為 PK：

ALTER TABLE public.ecdict ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

需要 Postgres 10 或更高版本。使用serial舊（或任何）版本的列：

ALTER TABLE public.ecdict ADD COLUMN id bigserial PRIMARY KEY;

看：

• 自動遞增表列

當然會觸發整個表的重寫。並且數字是由目前的物理行順序任意分配的。

或者，您可以在從 CSV 導入數據之前向表中添加一個serial或IDENTITY列，而不是在此過程中分配它，然後它會自動預設為序列號。

旁白：為什麼bigint？integer對於 100 萬行應該足夠好 - 除非您期望大量增長和/或流失。

要回答您的原始問題：您可以使用這樣的系統列ctid：

UPDATE ecdict c
SET    id = c2.seqnum
FROM  (SELECT ctid, row_number() OVER () AS seqnum FROM ecdict) c2
WHERE  c.ctid = c2.ctid;

看：

• 如何將 ctid 分解為頁碼和行號？

但是對於手頭的情況，第一個建議要好得多。

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