PostgreSQL 表字段顺序的 "修改"

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

在某些场景中,用户可能希望在原有字段的某个位置增加一个字段,例如
alter table test add column c1 int after id;
在id字段后面添加一个字段。
在PostgreSQL中,可以通过sql rewrite来做到同样的功能。
但是必须先了解PostgreSQL的物理存储,在PG中,数据是tuple组织的,每个tuple都是固定的storage layout,即字段存储的物理顺序是固定的,解释时是按照pg_attribute中存储的顺序。
那么怎么能做到用户看到的顺序是可以变的呢?
使用简单视图,即rewrite rule.

postgres=# create table tbl(id int, info text, crt_time timestamp);
CREATE TABLE
Time: 15.285 ms
postgres=# alter table tbl add column c1 int;
ALTER TABLE
Time: 12.872 ms
postgres=# create view v_tbl as select id,info,c1,crt_time from tbl;
CREATE VIEW
Time: 0.889 ms
postgres=# insert into v_tbl values (1,'test',2,now());
INSERT 0 1
Time: 1.208 ms
postgres=# select * from v_tbl
postgres-# ;
 id | info | c1 |          crt_time          
----+------+----+----------------------------
  1 | test |  2 | 2016-02-29 14:07:19.171928
(1 row)

Time: 0.544 ms
postgres=# select * from tbl;
 id | info |          crt_time          | c1 
----+------+----------------------------+----
  1 | test | 2016-02-29 14:07:19.171928 |  2
(1 row)

Time: 0.282 ms
postgres=# select attname,attnum,attisdropped from pg_attribute where attrelid ='tbl'::regclass;
 attname  | attnum | attisdropped 
----------+--------+--------------
 tableoid |     -7 | f
 cmax     |     -6 | f
 xmax     |     -5 | f
 cmin     |     -4 | f
 xmin     |     -3 | f
 ctid     |     -1 | f
 id       |      1 | f
 info     |      2 | f
 crt_time |      3 | f
 c1       |      4 | f
(10 rows)

Time: 0.708 ms
postgres=# alter table tbl drop column info;
ERROR:  cannot drop table tbl column info because other objects depend on it
DETAIL:  view v_tbl depends on table tbl column info
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
Time: 8.794 ms
postgres=# alter table tbl drop column info cascade; 
NOTICE:  drop cascades to view v_tbl
ALTER TABLE
Time: 1.561 ms
postgres=# \d v_t

postgres=# create view v_tbl as select id,c1,crt_time from tbl;
CREATE VIEW
Time: 2.248 ms
postgres=# select attname,attnum,attisdropped from pg_attribute where attrelid ='tbl'::regclass;
           attname            | attnum | attisdropped 
------------------------------+--------+--------------
 tableoid                     |     -7 | f
 cmax                         |     -6 | f
 xmax                         |     -5 | f
 cmin                         |     -4 | f
 xmin                         |     -3 | f
 ctid                         |     -1 | f
 id                           |      1 | f
 ........pg.dropped.2........ |      2 | t
 crt_time                     |      3 | f
 c1                           |      4 | f
(10 rows)

Time: 0.675 ms
postgres=# insert into v_tbl values (1,2,now());
INSERT 0 1
Time: 0.370 ms
postgres=# select * from v_tbl;
 id | c1 |          crt_time          
----+----+----------------------------
  1 |  2 | 2016-02-29 14:07:19.171928
  1 |  2 | 2016-02-29 14:09:18.499834
(2 rows)

Time: 0.295 ms
postgres=# select * from tbl;
 id |          crt_time          | c1 
----+----------------------------+----
  1 | 2016-02-29 14:07:19.171928 |  2
  1 | 2016-02-29 14:09:18.499834 |  2
(2 rows)

Time: 0.375 ms
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
SQL 关系型数据库 分布式数据库
PostgreSQL 在线修改数据类型 - online ddl 方法之一
标签 PostgreSQL , online ddl , trigger , ddl 事务 背景 有张表的主键id是serial,但现在不够了,需要升级成bigserial,有什么优雅的方法吗?我看下来好像会锁表很久(因为数据量挺大) 如果直接alter table,由于数据类型从4字节改成了8字节,而tuple结构是在METADATA里面的,不是每行都有,所以DEFORM需要依赖METADATA,目前来说,这种操作需要rewrite table。
3701 0
|
1月前
|
存储 Oracle 关系型数据库
PostgreSQL 清理表字段的备注脚本
PostgreSQL 清理表字段的备注脚本
|
关系型数据库 数据库 PostgreSQL
PostgreSQL 11 新特性解读 : Initdb/Pg_resetwal支持修改WAL文件大小
PostgreSQL 11 版本的一个重要调整是支持 initdb 和 pg_resetwal 修改 WAL 文件大小,而 11 版本之前只能在编译安装 PostgreSQL 时设置 WAL 文件大小。
8766 0
|
SQL 关系型数据库 数据库连接
PostgreSQL 修改数据库属性
PostgreSQL 修改数据库属性
106 0
|
关系型数据库 MySQL PostgreSQL
ruoyi数据源修改为PostgreSQL分页错误
ruoyi数据源修改为PostgreSQL分页错误
141 0
|
SQL 存储 缓存
Citus 分布式 PostgreSQL 集群 - SQL Reference(摄取、修改数据 DML)
Citus 分布式 PostgreSQL 集群 - SQL Reference(摄取、修改数据 DML)
128 0
|
关系型数据库 MySQL 数据库
PostgreSQL数据库实现表字段的自增
PostgreSQL数据库实现表字段的自增
1476 0
|
SQL 缓存 Java
修改PostgreSQL字段长度导致cached plan must not change result type错误
修改PostgreSQL字段长度可能导致cached plan must not change result type错误
6395 0
|
SQL 关系型数据库 MySQL
RDS Mysql数据修改后2小时之后才查到
问题背景: 用户的rds数据库,最近出现了这样一个问题:没有使用读写分离,提交一个update语句修改状态值,修改之后,当时查不到修改后的值,查到的还是修改前的值,过了2小时之后才可以查到,审计日志看sql语句是更新成功的。
1124 0
|
关系型数据库 PostgreSQL
PostgreSQL之连接数修改
当前总共正在使用的连接数 select count(1) from pg_stat_activity; 显示系统允许的最大连接数 show max_connections; 显示系统保留的用户数 show superuser_reserved_connections ; 按照用户分组查看 ...
4540 0

相关产品

  • 云原生数据库 PolarDB