postgresql 给表添加主键的最优方案

我就是我 2022-08-31 15:15 447阅读 0赞

os: centos 7.6  
db: postgresql 13

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.6.1810 (Core) 
    # 
    # su - postgres
    $ psql -c "select version();"
                                                     version                                                 
    ---------------------------------------------------------------------------------------------------------
     PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
    (1 row)

# 原始表 #

$ psql
    psql (13.3)
    Type "help" for help.
    
    postgres=# create table tmp_t0 (
      id varchar(50),
      name varchar(100)
      );
    
    postgres=# insert into tmp_t0(id,name) 
    select id::varchar,
           md5(id::varchar)
      from generate_series(1,10000) as id
    ;
    
    postgres=# insert into tmp_t0(id,name) 
    select '',
           md5(id::varchar)
      from generate_series(10001,20000) as id
    ;
    
    postgres=# 
    postgres=# select * from tmp_t0 limit 10;
     id |               name               
    ----+----------------------------------
     1  | c4ca4238a0b923820dcc509a6f75849b
     2  | c81e728d9d4c2f636f067f89cc14862c
     3  | eccbc87e4b5ce2fe28308fd9f2a7baf3
     4  | a87ff679a2f3e71d9181a67b7542122c
     5  | e4da3b7fbbce2345d7772b0674a318d5
     6  | 1679091c5a880faf6fb5e6087eb1b2dc
     7  | 8f14e45fceea167a5a36dedd4bea2543
     8  | c9f0f895fb98ab9159f51fd0297e236d
     9  | 45c48cce2e2d7fbdea1afc51c7c6ad26
     10 | d3d9446802a44259755d38e6d163e820
    (10 rows

# 添加主键 #

**第一步**、更新 id 列为null、或者空 的行

postgres=# update tmp_t0 set id=name where id is null or id = '';
    
    postgres=# select count(1) from tmp_t0 where id is null or id = '';
     count 
    -------
         0
    (1 row)

**第二步**、对 id 列添加非空约束 (毫秒级,只修改数据字典)

postgres=# alter table tmp_t0 alter column id set not null;

**第三步**、创建唯一性索引  
可以通过 ALTER TABLE 为 parallel\_workers 设置一个值直接控制着 CREATE INDEX 的并行，创建完后再恢复。  
记得一定要添加 concurrently 关键字

postgres=# create unique index concurrently pk_tmp_t0_id on tmp_t0(id);

**第四步**、创建主键约束  
对前面创建的索引做了一次 rename

postgres=# alter table tmp_t0 ADD CONSTRAINT pk_tmp_t0  PRIMARY KEY using index pk_tmp_t0_id;
    NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "pk_tmp_t0_id" to "pk_tmp_t0"
    ALTER TABLE

查看表的定义

参考:  
http://postgres.cn/docs/13/install-procedure.html