POSTGRESQL表调整字段顺序

在MySQL数据库⾥，在添加字段时可以添加到表的任意位置， 那么在POSTGRESQL⾥我们是否可以实现呢？答案是肯定可以的。

PG的语法⾥并没有类似AFTER, BEFORE的⼦句。总结：

第⼀种⽅法失败，甚⾄能导致数据库⾃动重启。

第⼆种⽅法可⾏，但如果数据量较⼤，业务停⽌的时间就会加长。

哪果数据量较⼩，可以考虑第⼆种⽅法。但如果数据量⼤，停机就没有办法接受了， 可以让业务修改代码来完成需求了。尝试⽅法⼀：

修改pg_attribute中的表字段位置：

postgres=# create table t_col(id int4, name varchar(9));CREATE TABLE ^postgres=# insert into t_col values(1,'a');INSERT 0 1

postgres=# insert into t_col values(2,'b');INSERT 0 1

postgres=# select attrelid,attname,attnum from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't_col'); attrelid | attname | attnum ----------+----------+-------- 353681 | tableoid | -7 353681 | cmax | -6 353681 | xmax | -5 353681 | cmin | -4 353681 | xmin | -3 353681 | ctid | -1 353681 | id | 1 353681 | name | 2(8 rows)

postgres=# select ctid, * from t_col; ctid | id | name -------+----+------ (0,1) | 1 | a (0,2) | 2 | b(2 rows)

postgres=# update pg_attribute set attnum=3 where attrelid=353681 and attname='id';UPDATE 1

postgres=# update pg_attribute set attnum=1 where attrelid=353681 and attname='name';UPDATE 1

postgres=# update pg_attribute set attnum=2 where attrelid=353681 and attname='id';UPDATE 1

postgres=# select attrelid,attname,attnum from pg_attribute where attrelid = (select relfilenode from pg_class where relname = 't_col'); attrelid | attname | attnum ----------+----------+-------- 353681 | tableoid | -7 353681 | cmax | -6 353681 | xmax | -5 353681 | cmin | -4 353681 | xmin | -3 353681 | ctid | -1 353681 | name | 1 353681 | id | 2(8 rows)

postgres=# \\d t_col

Table \"public.t_col\"

Column | Type | Modifiers --------+----------------------+----------- name | character varying(9) | id | integer

从上⾯的结果来看，字段顺序已经被调整了过来，尝试读取数据：

postgres=# select * from t_col;

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.!>

异常了，数据库被强制重启。

2017-12-05 15:35:11.451 CST [24682-13] [postgres@127.0.0.1]@postgres LOG: statement: select * from t_col;

2017-12-05 15:35:11.569 CST [5935-18] LOG: server process (PID 24682) was terminated by signal 11: Segmentation fault2017-12-05 15:35:11.569 CST [5935-19] DETAIL: Failed process was running: select * from t_col;2017-12-05 15:35:11.569 CST [5935-20] LOG: terminating any other active server processes

2017-12-05 15:35:11.570 CST [23383-2] WARNING: terminating connection because of crash of another server process

2017-12-05 15:35:11.570 CST [23383-3] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.2017-12-05 15:35:11.570 CST [23383-4] HINT: In a moment you should be able to reconnect to the database and repeat your command.2017-12-05 15:35:11.570 CST [25249-1] [postgres@127.0.0.1]@postgres FATAL: the database system is in recovery mode2017-12-05 15:35:11.574 CST [5935-21] LOG: archiver process (PID 23384) exited with exit code 1

2017-12-05 15:35:11.574 CST [23422-1] [repluser@192.168.252.3]@[unknown] WARNING: terminating connection because of crash of another server process

2017-12-05 15:35:11.574 CST [23422-2] [repluser@192.168.252.3]@[unknown] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and po2017-12-05 15:35:11.574 CST [23422-3] [repluser@192.168.252.3]@[unknown] HINT: In a moment you should be able to reconnect to the database and repeat your command.2017-12-05 15:35:11.575 CST [5935-22] LOG: all server processes terminated; reinitializing

2017-12-05 15:35:11.657 CST [25250-1] LOG: database system was interrupted; last known up at 2017-12-05 15:33:17 CST2017-12-05 15:35:12.373 CST [25250-2] LOG: database system was not properly shut down; automatic recovery in progress2017-12-05 15:35:12.376 CST [25250-3] LOG: redo starts at /FF0DBF80

2017-12-05 15:35:12.376 CST [25250-4] LOG: record with zero length at /FF0DC0702017-12-05 15:35:12.376 CST [25250-5] LOG: redo done at /FF0DC038

2017-12-05 15:35:12.395 CST [25250-6] LOG: MultiXact member wraparound protections are now enabled2017-12-05 15:35:12.397 CST [5935-23] LOG: database system is ready to accept connections2017-12-05 15:35:12.397 CST [252-1] LOG: autovacuum launcher started

其实t_col这张表已经损坏了，当对这张表操作时，都会出现重启的现象。尝试把表字段调回去：

postgres=# update pg_attribute set attnum=3 where attrelid=353681 and attname='name';UPDATE 1

postgres=# update pg_attribute set attnum=1 where attrelid=353681 and attname='id';UPDATE 1

postgres=# update pg_attribute set attnum=2 where attrelid=353681 and attname='name';UPDATE 1

postgres=# \\d t_col;

Table \"public.t_col\"

Column | Type | Modifiers --------+----------------------+----------- id | integer |

name | character varying(9) |

操作测试：

postgres=# insert into t_col values(3,'c');INSERT 0 1

postgres=# select * from t_col; id | name ----+------

1 | a 2 | b 3 | c(3 rows)

尝试⽅法⼆：

1. 禁⽌表写⼊数据。

postgres=# begin work;BEGIN

postgres=# lock table t_col in share mode;LOCK TABLE

2. 在另⼀个会话中创建要求字段顺序的表，并将原表数据插⼊到新表中：

postgres=# create table t_col_new(name varchar(9), id int4);CREATE TABLE

postgres=# insert into t_col_new select name, id from t_col;INSERT 0 3

postgres=# select * from t_col_new; name | id ------+---- a | 1 b | 2 c | 3(3 rows)

3. 对表进⾏RENAME操作：

postgres=# alter table t_col rename to t_col_old;ALTER TABLE

postgres=# alter table t_col_new rename to t_col;ALTER TABLE

postgres=# commit work;COMMIT

4. 数据效验：

postgres=# select * from t_col; name | id ------+---- a | 1 b | 2 c | 3(3 rows)