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

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

版本

# 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  (Red Hat 4.8.5-39), 64-bit (1 row) 

create table

$ psql psql (10.11) Type "help" for help. postgres=# \timing on Timing is on. postgres=# postgres=# drop table if exists tmp_t4; DROP TABLE Time: 313.287 ms postgres=# create table tmp_t4( id int8, name varchar(100), memo1 varchar(100), memo2 varchar(100) ); CREATE TABLE Time: 360.530 ms postgres=# insert into tmp_t4 select id, md5(id::varchar), md5(id::varchar), null from generate_series(1,) as id; INSERT 0  Time: 58346.187 ms (00:58.346) postgres=# select * from tmp_t4 limit 5; id | name | memo1 | memo2 ----+----------------------------------+----------------------------------+------- 1 | c4ca4238a0bdcc509a6f75849b | c4ca4238a0bdcc509a6f75849b | 2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | 4 | a87ff679a2f3e71d9181a67bc | a87ff679a2f3e71d9181a67bc | 5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | (5 rows) Time: 0.796 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) | | | | 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 --------+---------+-------------+---------+--------------  | tmp_t4 |  | r |  (1 row) Time: 0.579 ms 

alter column set default

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

postgres=# alter table tmp_t4 alter column memo1 set default 'aaa'; ALTER TABLE Time: 1129.620 ms (00:01.130) 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) | | | 'aaa'::character varying | 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 --------+---------+-------------+---------+--------------  | tmp_t4 |  | r |  (1 row) Time: 0.443 ms 

对列中含null进行 default 设置

postgres=# alter table tmp_t4 alter column memo2 set default 'aaa'; ALTER TABLE Time: 128.971 ms 

alter column drop default

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) | | | 'aaa'::character varying | extended | | memo2 | character varying(100) | | | 'aaa'::character varying | extended | | 

postgres=# alter table tmp_t4 alter column memo1 drop default; ALTER TABLE Time: 36.544 ms 

postgres=# alter table tmp_t4 alter column memo2 drop default; ALTER TABLE Time: 59.798 ms 

add column set default

postgres=# alter table tmp_t4 add column memo3 varchar(100) default 'aaa'; ALTER TABLE Time: 73482.788 ms (01:13.483) postgres=# select * from tmp_t4 limit 5; id | name | memo1 | memo2 | memo3 ----+----------------------------------+----------------------------------+-------+------- 1 | c4ca4238a0bdcc509a6f75849b | c4ca4238a0bdcc509a6f75849b | | aaa 2 | c81e728d9d4c2f636f067f89cc14862c | c81e728d9d4c2f636f067f89cc14862c | | aaa 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 | | aaa 4 | a87ff679a2f3e71d9181a67bc | a87ff679a2f3e71d9181a67bc | | aaa 5 | e4da3b7fbbce2345d7772b0674a318d5 | e4da3b7fbbce2345d7772b0674a318d5 | | aaa (5 rows) Time: 0.619 ms postgres=# select oid,relname,relfilenode,relkind,relfrozenxid from pg_class pc where pc.relname='tmp_t4'; oid | relname | relfilenode | relkind | relfrozenxid --------+---------+-------------+---------+--------------  | tmp_t4 |  | r |  (1 row) Time: 0.834 ms 

relfilenode 由之前 变为

add column set default 确实要慎重