PostgreSQL如何修改表的字段顺序

MySQL中我们可以使用类似下列的语句，在某个指定的字段后添加字段：alter table test add column c1 int after id;那么在PostgreSQL是否可以实现类似的功能呢？或者说修改表当前的字段顺序呢？比较简单的方法就是将表删除后重建。还有一种比较常见的方法就是通过视图来代替，例如：bill@bill=>create view v_t1 as select

foucus、

16624人浏览 · 2021-07-05 10:09:31

foucus、 · 2021-07-05 10:09:31 发布

MySQL中我们可以使用类似下列的语句，在某个指定的字段后添加字段：

alter table test add column c1 int after id;

那么在PostgreSQL是否可以实现类似的功能呢？或者说修改表当前的字段顺序呢？

比较简单的方法就是将表删除后重建。

还有一种比较常见的方法就是通过视图来代替，例如：

bill@bill=>create view v_t1 as select c1,c3,c2 from t1;
CREATE VIEW
bill@bill=>select * from v_t1 ;
 c1 |             c3             |                c2
----+----------------------------+----------------------------------
  1 | 2021-07-05 10:09:02.332355 | e0cf64a8f074a63b70be1855f91d167c
  2 | 2021-07-05 10:09:02.332538 | 4ff395ada53757d304a248cfe7390c53
  3 | 2021-07-05 10:09:02.332547 | ddd608e5f76eabd147928a608ecae5b1
  4 | 2021-07-05 10:09:02.332551 | 80cd52680d627c2be932c0639ffe56b2
  5 | 2021-07-05 10:09:02.332555 | b81ac3a5e393e0083e90b33d395bb5f4
(5 rows)

但是这种方法也有需要注意的地方，如果我们对基表进行修改时，视图也需要重建：

bill@bill=>alter table t1 drop column c3;
ERROR:  cannot drop column c3 of table t1 because other objects depend on it
DETAIL:  view v_t1 depends on column c3 of table t1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

bill@bill=>alter table t1 drop column c3 cascade;
NOTICE:  drop cascades to view v_t1
ALTER TABLE

那么除此之外还有没有什么别的办法呢？例如在Oracle中我们可以通过直接去修改col$表来调整表的字段顺序，那么pg中有没有类似的方案呢？

在pg中，表的字段信息存储在系统表pg_attribute中，数据是tuple组织的，每个tuple都是固定的storage layout，即字段存储的物理顺序是固定的，解释时是按照pg_attribute中存储的顺序。

那么我们是否能够直接修改pg_attribute表来修改表的字段顺序呢？

创建测试表：

bill@bill=>create table t1(c1 int,c2 int);
CREATE TABLE
bill@bill=>insert into t1 values(1,2);
INSERT 0 1

查看：
可以看到c1字段的字段位置是1，c2字段的位置是2。那么是不是接下来我们将该表修改就可以了呢？

bill@bill=>select attname,attnum,attisdropped from pg_attribute where attrelid ='t1'::regclass;
 attname  | attnum | attisdropped
----------+--------+--------------
 tableoid |     -6 | f
 cmax     |     -5 | f
 xmax     |     -4 | f
 cmin     |     -3 | f
 xmin     |     -2 | f
 ctid     |     -1 | f
 c1       |      1 | f
 c2       |      2 | f
(8 rows)

修改pg_attribute ：

bill@bill=>update pg_attribute set attnum = 3 where attrelid ='t1'::regclass and attname = 'c1';
UPDATE 1
bill@bill=>update pg_attribute set attnum = 1 where attrelid ='t1'::regclass and attname = 'c2';
UPDATE 1
bill@bill=>update pg_attribute set attnum = 2 where attrelid ='t1'::regclass and attname = 'c1';
UPDATE 1

再次检查：

bill@bill=>select * from t1;
 c2 | c1
----+----
  1 |  2
(1 row)

bill@bill=>select attname,attnum,attisdropped from pg_attribute where attrelid ='t1'::regclass;
 attname  | attnum | attisdropped
----------+--------+--------------
 tableoid |     -6 | f
 cmax     |     -5 | f
 xmax     |     -4 | f
 cmin     |     -3 | f
 xmin     |     -2 | f
 ctid     |     -1 | f
 c2       |      1 | f
 c1       |      2 | f
(8 rows)

似乎一些都是那么的理所当然，和我们预期的无异，直接修改pg_attribute就实现了表字段的顺序调整。但是真的如此吗？别急，我们接着往下测试。

创建测试表：

bill@bill=>create table t1(c1 int,c2 text);
CREATE TABLE
bill@bill=>insert into t1 values(1,'bill');
INSERT 0 1

修改pg_attribute：

bill@bill=>update pg_attribute set attnum = 3 where attrelid ='t1'::regclass and attname = 'c1';
UPDATE 1
bill@bill=>update pg_attribute set attnum = 1 where attrelid ='t1'::regclass and attname = 'c2';
UPDATE 1
bill@bill=>update pg_attribute set attnum = 2 where attrelid ='t1'::regclass and attname = 'c1';
UPDATE 1

再次验证：

bill@bill=>select attname,attnum,attisdropped from pg_attribute where attrelid ='t1'::regclass;
 attname  | attnum | attisdropped
----------+--------+--------------
 tableoid |     -6 | f
 cmax     |     -5 | f
 xmax     |     -4 | f
 cmin     |     -3 | f
 xmin     |     -2 | f
 ctid     |     -1 | f
 c2       |      1 | f
 c1       |      2 | f
(8 rows)

似乎也没什么问题，c1的attnum变成了2，c2变成了1，但是当我们再去查询该表时就会出问题了！

bill@bill=>select * from t1;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.