francs=> create table test_4 (id float8,name varchar(32)); CREATE TABLE francs=> insert into test_4 select generate_series(1,3000000),'a'; INSERT 03000000
francs=> select * from test_4 order by id desc limit 5; id | name ---------+------ 3000000 | a 2999999 | a 2999998 | a 2999997 | a 2999996 | a (5 rows)
francs=> /dt+ test_4 List of relations Schema | Name | Type | Owner | Size | Description --------+--------+-------+--------+--------+------------- francs | test_4 | table | francs | 115 MB | (1 row)
francs=> timing Timing is on.
francs=> /d test_4 Table"francs.test_4" Column | Type | Modifiers --------+-----------------------+----------- id | double precision | name | character varying(32) |
修改字段类型
1 2 3 4 5 6 7 8 9 10
francs=> alter table test_4 alter column id type integer ; ALTER TABLE Time: 23805.774 ms
francs=> /d test_4 Table "francs.test_4" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(32) |
备注： 由 float8 转换成 integer 类型花费了 23 秒，显然重写表了。
测试场景二: integer –> text
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
francs=> /d test_4 Table "francs.test_4" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(32) |
francs=> alter table test_4 alter column id type text; ALTER TABLE Time: 31735.712 ms
francs=> /d test_4 Table "francs.test_4" Column | Type | Modifiers --------+-----------------------+----------- id | text | name | character varying(32) |
备注： integer 类型转换成 text 类型花费了 31 秒左右，显然重写表了。
测试场景三: text –> integer
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
francs=> /d test_4; Table "francs.test_4" Column | Type | Modifiers --------+-----------------------+----------- id | text | name | character varying(32) |
francs=> alter table test_4 alter column id type integer using (id::integer); ALTER TABLE Time: 34431.975 ms francs=> /d test_4 Table "francs.test_4" Column | Type | Modifiers --------+-----------------------+----------- id | integer | name | character varying(32) |
