PostgreSQL 表字段顺序的 "修改"

最新推荐文章于 2025-05-15 14:26:16 发布

weixin_34221073

最新推荐文章于 2025-05-15 14:26:16 发布

阅读量8k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：https://yq.aliyun.com/articles/7176

本文介绍如何在PostgreSQL中通过创建视图实现字段显示顺序的调整，包括字段的添加与删除操作，并展示了具体的SQL语句及执行效果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在某些场景中，用户可能希望在原有字段的某个位置增加一个字段，例如
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