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 fault 2017-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 mode 2017-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 possibly corrupted shared memory. 2017-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 CST 2017-12-05 15:35:12.373 CST [25250-2] LOG: database system was not properly shut down; automatic recovery in progress 2017-12-05 15:35:12.376 CST [25250-3] LOG: redo starts at 89/FF0DBF80 2017-12-05 15:35:12.376 CST [25250-4] LOG: record with zero length at 89/FF0DC070 2017-12-05 15:35:12.376 CST [25250-5] LOG: redo done at 89/FF0DC038 2017-12-05 15:35:12.395 CST [25250-6] LOG: MultiXact member wraparound protections are now enabled 2017-12-05 15:35:12.397 CST [5935-23] LOG: database system is ready to accept connections 2017-12-05 15:35:12.397 CST [25254-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)
