PostgreSQL: 是否可以更改表中已有字段的顺序?

今天有朋友问 “ 在 PostgreSQL 的表增加字段后,能否更改字段的顺序? ”,后来查了下资料,并做了些测试,并没有找到能够直接在原表的基础上实现已有字段顺序更改的方法,当然可以通过间接方法来实现。

暂且不论这个需求的原因,个人觉得调整表字段顺序的需求比较奇怪,既然有人问题,不妨测试下。

关于 pg_attribute 系统表

首先看下系统表 pg_catalog.pg_attribute,这个系统表记录的是数据库所有表的字段信息。其中 pg_attribute.attnum 存储的是表字段的顺序,那么是否可以通过更改这个值来实现目的呢?接着往下看;

创建测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
francs=> create table test_6(col1 int4,col2 int4,col3 int4);  
CREATE TABLE

francs=> insert into test_6 values (1,2,3);
INSERT 0 1
francs=> insert into test_6 values (4,5,6);
INSERT 0 1
francs=> select * from test_6;
col1 | col2 | col3
----+------+------
1 | 2 | 3
4 | 5 | 6
(2 rows)

查看字段顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
francs=> select attrelid,attname,attnum From pg_attribute where attrelid='francs.test_6'::regclass;  
attrelid | attname | attnum
----------+----------+--------
24700 | tableoid | -7
24700 | cmax | -6
24700 | xmax | -5
24700 | cmin | -4
24700 | xmin | -3
24700 | ctid | -1
24700 | col1 | 1
24700 | col2 | 2
24700 | col3 | 3
(9 rows)

备注:col1 的 attnum值为 1,col2 的 attnum值为 2,col3 的 attnum值为 3,,正好是字段的顺序值。其中 attnum 值为负数的行表示表的隐含字段,例如 ctid,xmin,cmin 等。

修改字段的 attnum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
francs=> \c francs postgres  
You are now connected to database "francs" as user "postgres".

francs=# update pg_attribute set attnum=4 where attrelid='francs.test_6'::regclass and attname='col3';
UPDATE 1
francs=# update pg_attribute set attnum=3 where attrelid='francs.test_6'::regclass and attname='col2';
UPDATE 1
francs=# update pg_attribute set attnum=2 where attrelid='francs.test_6'::regclass and attname='col3';
UPDATE 1

francs=# select * from francs.test_6;
col1 | col3 | col2
----+------+------
1 | 2 | 3
4 | 5 | 6
(2 rows)

备注:修改系统表 pg_attribute ,更改表 francs.test_6 字段的 attnum 值,结果发现只是字段名称换了下顺序,而字段中的值却没有更改,显然通过修改系统表 pg_attribute.attnum 值是行不通的,更进一步,假设这步成功了,如果表字段上有索引,或者约束,那么还得修改相应的系统表,显然修改数据库系统表的做法是危险的,容易给数据库带来灾难,万不得已,不建议这么做。

既然直接修改系统表字段顺序的方法行不通,那么可以通过其它间接的方法,这里想到了两种,第一种是重建表,即新建表结构再把老表数据导进去。第二种是新建一个符合规则的视图,以后应用程序不直接查原表,而是查视图。

重建表的方法

重建表的方法很多,例如在创建好目标表之后可以使用“insert into ” 方式或者 copy 方式导入数据。这里不再详述

重建视图的方法

创建目标视图,使字段顺序为目标的字段顺序,从而隐含了视图引用表的字段实际物理顺序,创建视图 SQL 略。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%