PostgreSQL如何修改表的字段顺序

2023-12-16 19:02| 来源: 网络整理| 查看: 265

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：

再次验证：

似乎也没什么问题，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.

可以看到直接core掉了，这是为什么呢？

因为我们修改pg_attribute只是修改了定义，并没有实际修改数据的存储，所以在解释的时候就会出现问题，例如上面在解释时实际存储的int值被解释成text。除此之外，如果字段上面存在约束、索引这些更是会出现问题。

所以可以得出结论：不能直接修改pg_attribute来调整字段的顺序！

如果你真的因为某些需求需要调整字段的顺序，建议还是重建表或者通过视图的方式来实现。

参考链接： https://www.postgresql.org/docs/13/catalog-pg-attribute.html https://github.com/digoal/blog/blob/master/201602/20160229_01.md

【本文地址】

公司简介

联系我们