主页 / dba / 问题 / 300365
Accepted
Dolphin
Asked: 2021-09-30 18:55:38 +0800 CST

如何将序列号添加到表中？

  • 772

我有一个表有超过 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值的列？

postgresql primary-key

1 个回答

  1. Best Answer

    像这样添加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;

    看：

    但是对于手头的情况，第一个建议要好得多。

    • 3

相关问题