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 po 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; autom
atic 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;
insert语句字段顺序name | id
------+----
a    |  1
b    |  2
c    |  3
(3 rows)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。