postgresql新增非空默认值字段是否需要重写表

于 2023-11-22 16:13:06 发布
阅读量617 收藏
点赞数 5
分类专栏： postgresql 文章标签： postgresql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/forrest_hu/article/details/134555979
版权

简介： PostgreSQL 10 版本前表新增不带默认值的DDL不需要重写表，只需要更新数据字典，因此DDL能瞬间执行，如下: ALTER TABLE table_name ADD COLUMN flag text; 如果新增的字段带默认值，则需要重写表，表越大，执行时间越长，如下。

PostgreSQL 10 版本前表新增不带默认值的DDL不需要重写表，只需要更新数据字典，因此DDL能瞬间执行，如下:

ALTER TABLE table_name ADD COLUMN flag text;

如果新增的字段带默认值，则需要重写表，表越大，执行时间越长，如下。

ALTER TABLE table_name ADD COLUMN flag text DEFAULT 'default values';

生产环境下给大表添加带 Default 值的字段将非常吃力，通常分两步进行：

  1. 第一步: 先添加不带 Default值的字段。
  2. 第二步: 写函数批量刷新新增字段的默认值。

上述第二步比较麻烦，也可以在业务低谷或申请停服窗口一次性完成带DEFAUL值字段的新增。

PostgreSQL 11 版本这方面进一步增强，表新增带非空默认值的字段不再需要重写表，Release 中的说明如下：

Release中的说明

Allow ALTER TABLE to add a column with a non-null default without a table rewrite

本文分别在 10 版本和 11 版本进行测试。

PostgreSQL 10 版本

创建测试表并插入1000万数据，如下。

[pg10@pghost1 ~]$ psql mydb pguser
psql (10.0)
Type "help" for help.

mydb=> CREATE TABLE t1(id int4, name text);
CREATE TABLE

mydb=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000

mydb=> ANALYZE t1;
ANALYZE

查看表的 relfilenode 和 relpages 信息，relfilenode 表示表的物理文件号。

mydb=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       25672 |    73530
(1 row)

新增带默认值的非空字段，如下。

mydb=> \timing
Timing is on.

mydb=> ALTER TABLE t1 ADD COLUMN flag text DEFAULT 'abcdefg';
ALTER TABLE
Time: 15540.002 ms (00:15.540)

执行时间较长，需要15秒左右。

表分析后再次查看表的 relfilenode 和 relpages信息

mydb=> ANALYZE t1;
ANALYZE

mydb=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       25679 |    83334
(1 row)

发现 relfilenode 有变化 ，之前的 relfilenode 值为 25672 ，说明表被重写。另一方面 relpages 变大了。

PostgreSQL 11 版本

创建测试表并插入1000万数据，如下。

[pg11@pghost2 ~]$ psql francs francs
psql (11beta3)
Type "help" for help.

francs=> CREATE TABLE t1(id int4, name text);
CREATE TABLE

francs=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000

francs=> ANALYZE t1;
ANALYZE

查看表的 relfilenode 和 relpages信息，如下:

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       16802 |    73530

新增带默认值的非空字段，如下。

francs=> \timing
Timing is on.

francs=> ALTER TABLE t1 ADD COLUMN flag text DEFAULT 'abcdefg';
ALTER TABLE
Time: 40.743 ms

执行时间只需要 40 ms，瞬间完成。

表分析后再次查看表的 relfilenode 和 relpages信息

francs=> ANALYZE t1;
ANALYZE

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       16802 |    73530
(1 row)

发现 relfilenode 没有变化，依然是 16802，同时 relpages 也没有变化。

增加1000字段

PostgreSQL 11 版本给表 t1 增加了一个带默认值的字段后表占用空间没有变化，是不是增加的字段数不够多？接着往下测试，增加1000个带默认值的字段，看看情况如何？

创建测试表并插入1000万测试数据，如下:

francs=> DROP TABLE t1;
DROP TABLE

francs=> CREATE TABLE t1(id int4, name text);
CREATE TABLE

francs=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000

francs=> ANALYZE t1;
ANALYZE

查看表的 relfilenode 和 relpages信息，如下:

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       34187 |    73530
(1 row)

查看表大小，如下：

francs=> SELECT pg_size_pretty(pg_relation_size('t1'));
 pg_size_pretty
----------------
 574 MB
(1 row)

创建函数，此函数用来给表 t1 添加 1000 个带默认值的字段，如下:

CREATE OR REPLACE FUNCTION  add_column() RETURNS INTEGER  AS  $BODY$
DECLARE
    column_name       text;
    default_value     text;
BEGIN
         default_value:= repeat(md5('1'),10);

       FOR i in 1..1000 LOOP
         column_name:= 'flag' || i;
         EXECUTE  
$$
 ALTER TABLE t1 ADD COLUMN 
$$
 || column_name  || 
$$
 text default' 
$$
 || default_value  || 
$$
'
$$
 ;
       END LOOP;

       RETURN 1;
END
$BODY$  LANGUAGE 'plpgsql';

执行函数，如下：

francs=> SELECT add_column();
 add_column
------------
          1
(1 row)

这时表t1已增加了1000个字段，如下：

francs=> SELECT * FROM t1 LIMIT 1;
-----------------------------------------[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------

id       | 1
name     | 1_ALTER TABLE TEST
flag1    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag2    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag3    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag4    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag5    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag6    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag7    |  c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
...省略

查看表 relfilenode 和 relpages，没有变化。

francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
 relname | relfilenode | relpages
---------+-------------+----------
 t1      |       34187 |    73530
(1 row)

再次确认表大小，依然还是 574MB。

francs=> SELECT pg_size_pretty(pg_relation_size('t1'));
 pg_size_pretty
----------------
 574 MB
(1 row)

从以上看出给表t1增加了1000个带默认值的字段后，t1表大小依然没有变化。

参考

胖胖小李胡
关注
  • 5
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
2024年网络安全山东省赛-SMB信息收集解析（中职组）_使用访问工具对服务器服务访问,将服务器中管理员桌面的文本文档里的内容作为 flag
2401_84252281的博客
04-26 861
任务环境说明：服务器场景：Server1。
PostgreSQL获取表名和字段名
12-13
以下查询展示了名为`tablename`的表的所有字段，包括其注释（如果存在）、数据类型、字段名称和是否允许为空： ```sql SELECT col_description(a.attrelid, a.attnum) as comment, format_type(a.atttypid, a....
关系型数据库(SQL)默认值操作
最新发布
weixin_50914566的博客
09-27 2867
将上述命令中的"表名"替换为需要查询的表名，"字段名"替换为需要查询默认值的字段名称即可。这将返回一个结果集，其中包含"age"字段及其对应的默认值。请将上述语句中的"表名"替换为要修改的表名，"列名"替换为要添加默认值的字段名，"默认值"替换为要设置的默认值。请将上述语句中的 "表名" 替换为要修改的表名，"字段名" 替换为要清除默认值的字段名。这将会清除 "users" 表中 "age" 字段的默认值。这将为"users"表中的"age"字段设置默认值为18。
PG数据库非空判断语句（类似mysql中的ifnull）
qq421439340的博客
10-15 2525
COALESCE (expression_1, expression_2, expression_2,...,expression_n) 依次参考各参数表达式，遇到非null值即停止并返回该值。 如果所有的表达式都是空值，最终将返回一个空值。
postgresql查找字段空、不为空的方法总结
u011250186的博客
02-18 1万+
在使用pgsql时，想要取到某些字段不为空或者为空的数据，可以用以下方法： 1、不为空 Select * From table Where id<>'' Select * From table Where id!='' 2、为空 Select * From table Where id='' Select * From ta...
PG给某个字段增加/删除非空约束
u010410697的博客
08-11 8350
1. 删除非空约束 alter table xxx alter xxx drop not null; 2. 添加非空约束 alter table xxx alter xxx set not null;
PostgreSQL教程(一)：数据表详解
12-16
7. 修改表结构：有时我们需要对已创建的表进行修改，比如添加新字段、更改字段类型或删除字段。在PostgreSQL中，可以使用`ALTER TABLE`语句来实现： - 添加字段： 代码如下: ```sql ALTER TABLE products ADD ...
GP(postgreSql)数据库获得字段信息的语句
03-28
GP(postgreSql)数据库获得字段信息的语句,很实用的语句
CodeSmith8.0及MySql、Postgresql字段注释修复
03-16
《CodeSmith8.0与MySQL、PostgreSQL数据库字段注释修复指南》 CodeSmith8.0是一款强大的代码生成工具，它能帮助开发者自动生成各种编程语言的代码，提高开发效率。然而，在使用过程中，可能会遇到一个问题，即在...
PostgreSQL设置字段默认值
weixin_42625516的博客
10-25 3351
原文链接：https://blog.csdn.net/weixin_41287260/article/details/125025780。版权声明：本文为CSDN博主「秦时明月之君临天下」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。--为表 tablename 添加 name1 字段,设置默认值为 1。--删除 tablename 的 name1 字段的默认值。PostgreSQL设置字段默认值。
pg数据库移除字段非空约束
weixin_45386794的博客
02-23 2150
用法：alter table 表名 alter 字段名 drop not null; 示例： alter table psmaterialdelivery alter senwhid drop not null;
pgsql 添加字段同时添加约束（not null）
weixin_43224069的博客
08-26 3046
新增字段同时设置约束。 1.新增字段时要设置不为空（not null）的限制时，必须要给个默认值 2.修改/删除字段约束
MySQL和PostgreSQL/pgsql中查询指定表指定字段的默认值
即使放弃那棵树，也无法拥抱森林
02-27 5980
MySQL和PostgreSQL/pgsql中查询指定表指定字段的默认值 写代码时，需要给实体类赋值，然后再进行Insert或者Update。 Insert时，一些字段完全可以去查询，设计数据库时设置的默认值，而不是在代码里把值写死。 而且在代码中需要逐个给字段赋值，不赋值的字段ORM生成的SQL里真的会给一个NULL值进去，有些ORM就是不能配置Insert时的[忽略字段]。
【pgsql-参数详解1】PostgreSQL默认参数值
kouryoushine的博客
03-26 8878
如果pgsql的参数文件都是用默认值，各个参数值是多少了，这里贴出来给大家做个参考。 安装pgsql后不做任何配置。 name | setting | des...
PostgreSQL 11 新特性之快速增加包含非空默认值的列
weixin_44519342的博客
01-28 732
PostgreSQL 11 新特性之快速增加包含非空默认值的列
mysql DDL语句上字段的非空约束和有默认值、无默认值的区别
zx1323的博客
08-12 5422
主要观察age字段 准备两条insert语句 INSERT INTO `user` (name, age) VALUES ('张三', NULL); INSERT INTO `user` (name) VALUES ('张三'); 情况一：运行字段值为 NULL 1.无默认值 CREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO...
【PG内核】pg11秒级新增非空默认字段的实现方法
weixin_33933118的博客
01-21 521
2019独角兽企业重金招聘Python工程师标准>>> ...
postgresql 判断字段是否为空
热门推荐
xiuxiuzhuanshu的博客
12-06 3万+
select COALESCE(tran.rm_amt,0) rm_amt select COALESCE(null, 0) as test 结果 test 0
postgresql 创建表设置默认值
08-29
在PostgreSQL中，可以使用ALTER TABLE语句来为表设置默认值。例如，你可以使用以下语句在department表中添加一个名为"budget"的列，并将其默认值设置为130000： ALTER TABLE department ADD budget int DEFAULT ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值