表增加字段

由于需求变更，需要增加字段。但是呢，发现postgre中使用的是标准的sql语法，并没有指定在哪个位置插入字段的语法，默认的ALTER TABLE table_name ADD COLUMN new_field语法，不支持类似mysql的after语法，只能插在表的最后。网上大概找了下，也没有什么优化的方法。

有如下变通：1、新建表，复制旧数据到新表，删除旧表，2、或者创建视图，来保证顺序一致。（建立一个视图）

本文记录一下方式1的迁移方法。

结论

对表进行etl转换，最好将计算字段放到表的前面，这样方便后续的扩展。

一般语法

ALTER TABLE table_name ADD COLUMN kafka_time timestamp without time zone;

只能在末尾增加字段。

迁移前

临时表结构

create table i1_import_raw (
    ship_id varchar(20),
    clraddr text,
    -- 以下为新增字段
    psmdb_ft smallint,
    psmdb_loc smallint,
    Recv_Prov varchar(32),
    Recv_City varchar(32),
    Recv_Coty varchar(32)
) with (appendonly=true, compresslevel=5, orientation=column, compresstype=zlib)
Distributed by (ship_id);

目标表结构

create table i1_order (    
    ship_id varchar(20),
    clraddr text,
    -- 欲在此处增加新字段
    batch_time timestamp without time zone,  -- 批处理转换成时间戳
) with (appendonly=true, compresslevel=5, orientation=column, compresstype=zlib)
Distributed by (ship_id)

数据转换脚本

由于导入数据的时候，先将数据导入到临时表，然后执行如下，将临时表的数据导入到正式表。

psql -d develop -h  localhost -U gpadmin  -p 5432  -c "insert into i1_$table select *,to_timestamp(substring(batch from 3),'yyyyMMddhh24miss') as batch_time from i1_import_raw;"

正是由于，当时建表的时候，没有想到新增字段这样的需求，错误的将计算字段放到了末尾。结果导致新增字段时，需要重新迁移表。

迁移

临时表迁移

可以直接在表后面，增加字段。

ALTER TABLE i1_import_raw ADD COLUMN psmdb_ft   smallint;
ALTER TABLE i1_import_raw ADD COLUMN psmdb_loc smallint;
ALTER TABLE i1_import_raw ADD COLUMN Recv_Prov  varchar(32);
ALTER TABLE i1_import_raw ADD COLUMN Recv_City  varchar(32);
ALTER TABLE i1_import_raw ADD COLUMN Recv_Coty  varchar(32);

正式表，却不能使用上面的方式来增加字段。

-- 无法工作
ALTER TABLE i1_order ADD COLUMN psmdb_ft smallint after ;
-- 上面只能在最后添加，计算字段 batch_time 结果在中间，行不通。

ALTER TABLE ADD COLUMN只会在最后添加新列,作为最后一列. 要在另一个位置创建新列,您需要重新创建表并从此新表中的旧/当前表中复制数据.

正式表

重建表结构

create table i1_order2 (
    batch_time timestamp without time zone,  -- 批处理转换成时间戳
    -- 注意，上面存放计算字段，下面存放临时表一样的结构，下次就可以直接 add修改了。
    ship_id varchar(20),
    clraddr text,
    -- 新增的字段
    psmdb_ft smallint,
    psmdb_loc smallint,
    Recv_Prov varchar(32),
    Recv_City varchar(32),
    Recv_Coty varchar(32)
) with (appendonly=true, compresslevel=5, orientation=column, compresstype=zlib)
Distributed by (ship_id);

迁移过程

-- 为了匹配上字段，每个都逐一增加列名
-- 一般方式
insert into i1_order2 select * from i1_order ;
-- 先测试 
insert into i1_order2 (batch_time,ship_id) select batch_time,ship_id from i1_order limit 10;
-- 整体迁移
-- 字段列表   如 fields1,fields2
insert into i1_order2 (字段列表) select 字段列表  from i1_order;
-- 经测试发现，及时部分字段，insert部分也能省略
insert into i1_order2 select 字段列表  from i1_order;

-- 切换表名
drop table i1_order;
alter table i1_order2 rename to i1_order;
-- 其他，如统计新表的内容等。

替换脚本

转换的时候，将计算字段放到最前面。这样方便后续增加字段。

# 调整一下计算字段的顺序
psql -d  develop   -h  localhost   -U gpadmin  -p 5432  -c "insert into i1_$table select to_timestamp(substring(batch from 3),'yyyyMMddhh24miss'),* as batch_time from i1_import_raw;"