PostgreSQL 在执行 Alter table table_name drop column 命令后，并没有在物理上删除这个列，而只是改下这个列的标志，可以通过修改 pg_attribute 属性进行恢复，下面是详细步骤：
先看 pg_attribute 几个关键的列
Name
Type
References
Description
attrelid
oid
pg_class.oid
The table this column belongs to
attname
name
The column name
atttypid
oid
pg_type.oid
The data type of this column
attnum
int2
The number of the column. Ordinary columns are numbered from 1 up. System columns, such as oid, have (arbitrary) negative numbers
attisdropped
bool
This column has been dropped and is no longer valid. A dropped column is still physically present in the table, but is ignored by the parser and so cannot be accessed via SQL
测试场景一: 删除列后恢复
创建测试表并插入数据
1 2 3 4
skytf=> create table test_26 (id integer,name varchar(32),machine varchar(32)); CREATE TABLE skytf=> insert into test_26 select generate_series(1,10000),'francs','sky'; INSERT 0 10000
查看表结构
1 2 3 4 5 6 7
skytf=> /d test_26 Table "skytf.test_26" Column | Type | Modifiers ---------+-----------------------+----------- id | integer | name | character varying(32) | machine | character varying(32) |
查看一条测试数据
1 2 3 4 5
skytf=> select * from test_26 limit 1; id | name | machine ----+--------+--------- 1 | francs | sky (1 row)
查看表信息
1 2 3 4 5 6 7 8 9 10 11
skytf=> select oid,relname from pg_class where relname='test_26'; oid | relname ----------+--------- 14280890 | test_26 (1 row)
skytf=> select attrelid,attname,atttypid,attisdropped ,attnum from pg_attribute where attrelid=14280890 and attnum=2; attrelid | attname | atttypid | attisdropped | attnum ----------+---------+----------+--------------+-------- 14280890 | name | 1043 | f | 2 (1 row)
删除列
1 2
skytf=> alter table test_26 drop column name; ALTER TABLE
再次查看 pg_attribute 列信息
1 2 3 4 5
skytf=> select attrelid,attname,atttypid,attisdropped ,attnum from pg_attribute where attrelid=14280890 andattnum=2; attrelid | attname | atttypid | attisdropped | attnum ----------+------------------------------+----------+--------------+-------- 14280890 | ........pg.dropped.2........ | 0 | t | 2 (1 row)
查看表结构
1 2 3 4 5 6
skytf=> /d test_26 Table "skytf.test_26" Column | Type | Modifiers ---------+-----------------------+----------- id | integer | machine | character varying(32) |
skytf=> /c skytf postgres You are now connected to database "skytf" as user "postgres".
skytf=# update pg_attribute setattname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 andattnum=2; UPDATE 1
skytf=# /d skytf.test_26; Table "skytf.test_26" Column | Type | Modifiers ---------+-----------------------+----------- id | integer | name | character varying(32) | machine | character varying(32) |
skytf=> select max(id) from test_26; max ------- 10000 (1 row)
skytf=> /d test_26; Table "skytf.test_26" Column | Type | Modifiers ---------+-----------------------+----------- id | integer | name | character varying(32) | machine | character varying(32) |
skytf=> select * from test_26 limit 1; id | name | machine ----+--------+--------- 1 | francs | sky (1 row)
删除列
1 2
skytf=> alter table test_26 drop column name; ALTER TABLE
查看数据
1 2 3 4 5
skytf=> select * from test_26 limit1; id | machine ----+--------- 1 | sky (1 row)
插入一条数据
1 2
skytf=> insert into test_26(id,machine) values (10001,'sky10001'); INSERT 0 1
恢复删除的列
1 2 3 4 5 6 7 8 9 10 11 12 13 14
skytf=> /c skytf postgres You are now connected to database "skytf" as user "postgres".
skytf=# update pg_attribute setattname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 andattnum=2; UPDATE 1
skytf=# /c skytf skytf You are now connected to database "skytf" as user "skytf".
skytf=> select * from test_26 where id=10001; id | name | machine -------+------+---------- 10001 | | sky10001 (1 row)
skytf=> /d test_26 Table"skytf.test_26" Column | Type | Modifiers ---------+-----------------------+----------- id | integer | name | character varying(32) | machine | character varying(32) | skytf=> select * From test_26 where id=1; id | name | machine ----+--------+--------- 1 | francs | sky (1 row)
删除列
1 2
skytf=> alter table test_26 drop column name; ALTER TABLE
修改一条记录
1 2 3 4 5 6 7 8
skytf=> update test_26 set machine ='sky_001'where id=1; UPDATE1
skytf=> select * from test_26 where id=1; id | machine ----+--------- 1 | sky_001 (1 row)
恢复删除的列
1 2 3 4 5 6 7 8 9 10 11
skytf=> /c skytf postgres You are now connected to database "skytf" as user "postgres".
skytf=# update pg_attribute setattname='name' ,atttypid=1043,attisdropped='f' where attrelid=14280890 andattnum=2; UPDATE 1
skytf=# select * from skytf.test_26 where id=1; id | name | machine ----+------+--------- 1 | | sky_001 (1 row)
skytf=> /d test_26 Table "skytf.test_26" Column | Type | Modifiers ---------+-----------------------+----------- id | integer | name | character varying(32) | machine | character varying(32) |
