postgresql 数据库 alter table alter column set not null 的一些实践

os: centos 7.4
db: postgresql 10.11

创建表后，有时需要对表进行 set not null 或者 drop not null 设置。

版本

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

create table

$ psql
psql (10.11)
Type "help" for help.

postgres=# 
postgres=# drop table if exists tmp_t4;
postgres=# 
postgres=# create table tmp_t4( 
id    int8,
name  varchar(100),
memo1 varchar(100),
memo2 varchar(100)
);

postgres=# 
postgres=# insert into tmp_t4 
select id,
       md5(id::varchar),
       md5(id::varchar),
	   null 
  from generate_series(1,1000000) as id;

postgres=# select * from tmp_t4 limit 5;

 id |               name               |              memo1               | memo2 
----+----------------------------------+----------------------------------+-------
  1 | c4ca4238a0b923820dcc509a6f75849b | c4ca4238a0b923820dcc509a6f75849b | 
  2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | 
  3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 
  4 | a87ff679a2f3e71d9181a67b7542122c | a87ff679a2f3e71d9181a67b7542122c | 
  5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | 
(5 rows)

postgres=# \d+ tmp_t4
                                          Table "public.tmp_t4"
 Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                 |           |          |         | plain    |              | 
 name   | character varying(100) |           |          |         | extended |              | 
 memo1  | character varying(100) |           |          |         | extended |              | 
 memo2  | character varying(100) |           |          |         | extended |              | 

postgres=# select oid,relname,relfilenode,relkind,relfrozenxid 
from pg_class pc where pc.relname='tmp_t4';

  oid   | relname | relfilenode | relkind | relfrozenxid 
--------+---------+-------------+---------+--------------
 123860 | tmp_t4  |      123860 | r       |       406588
(1 row)

set not null

对列中不含null进行 set not null 设置

postgres=# \timing on
Timing is on.
postgres=# alter table tmp_t4 alter column memo1 set not null;
ALTER TABLE
Time: 425.849 ms

postgres=# \d+ tmp_t4
                                          Table "public.tmp_t4"
 Column |          Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | bigint                 |           |          |         | plain    |              | 
 name   | character varying(100) |           |          |         | extended |              | 
 memo1  | character varying(100) |           | not null |         | extended |              | 
 memo2  | character varying(100) |           |          |         | extended |              | 

postgres=# select oid,relname,relfilenode,relkind,relfrozenxid 
from pg_class pc where pc.relname='tmp_t4';

  oid   | relname | relfilenode | relkind | relfrozenxid 
--------+---------+-------------+---------+--------------
 123860 | tmp_t4  |      123860 | r       |       406588
(1 row)

Time: 0.527 ms

对列中含null进行 set not null 设置

postgres=# alter table tmp_t4 alter column memo2 set not null;
ERROR:  column "memo2" contains null values
Time: 0.648 ms

报错，不能设置,对 0.648ms有点费解，怎么能在这么短时间发现有null 值

postgres=# update tmp_t4 set memo2=memo1 where id between 1 and 1000000;
UPDATE 1000000
Time: 16211.450 ms (00:16.211)

postgres=# update tmp_t4 set memo2=null where id between 1000000 and 1000000;
UPDATE 1
Time: 534.788 ms

postgres=# alter table tmp_t4 alter column memo2 set not null;
ERROR:  column "memo2" contains null values
Time: 72.370 ms

从时间上来看是要扫描数据列判断

drop not null

postgres=# alter table tmp_t4 alter column memo1 drop not null;
ALTER TABLE
Time: 319.677 ms
postgres=# 

postgres=# alter table tmp_t4 alter column memo2 drop not null;
ALTER TABLE
Time: 82.202 ms

参考:
http://postgres.cn/docs/10/sql-altertable.html