Postgresql实战经验之alter table 开小差了

Postgresql实战经验之alter table 开小差了

今天需要将一张有数据的表中一个字段varchar 类型转换为timestamp类型，但是pg的alter table 语句却开小差，出现了两种问题，翻了pg10.5中文手册、很多博客文档，做了对比实验，哎，可谓费了九牛二虎之力才解决问题。

1.alter table 修改表定义（DDL操作）

官方文档关于alter table 的用法

ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
    RENAME TO new_name
ALTER TABLE name
    SET SCHEMA new_schema

这里的 action 是下列之一：

    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
    DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column TYPE type [ USING expression ]
    ALTER [ COLUMN ] column SET DEFAULT expression
    ALTER [ COLUMN ] column DROP DEFAULT
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column SET STATISTICS integer
    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint
    DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OWNER TO new_owner
    SET TABLESPACE new_tablespace

　　

其中，修改表字段是用

alter table test alter colunm_name type colunm_type；

　　

2.将表alter_table 中var_date字段类型varchar 转换为timestamp

实验步骤：
1）建表
2）插入数据
3）使用修改表字段语句进行字段类型转换

出现错误：error：cloumn “var_date” cannot be autmatically to type timestamp without time zone Hint:You might need to specify "using var_date::timestamp without time zone"

查阅资料：

This form changes the type of a column of a table. Indexes and simple table constraints involving the column willbe automatically converted to use the new column type by reparsing the originally supplied expression. The optional COLLATE clause specifies a collation for the new column; if omitted, the collation is the default for the new column type. The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

大致意思是：转换类型的时候有隐含类型转换的时候，会自动转换，如果没有，那么就必须使用using显性指定一下转换规则

那么我们使用如alter table 就成功转换：

alter table alter_table alter var_date type timestamp using var_date::timestamp without time zone

　　

结果如下：

3.失败二alter table

上述显式修改表字段，我以为这样，我的正式表就可以修改成功，结果又出现另一种问题，如图所示：

显式修改字段类型出现错误如下：

error：invalid input syntax for type timestamp;

查了好多官方文档，也没有这类解释，后来经过高人指点，可能表中这个数据是null类型，转换不了，可以先将表中这个数据转换为timestamp类型，然后再用alter 语句转换数据类型。

实验如下：
1）先查表中字段这个数据，果然是"NULL"
2）修改表中数据为”timestamp“类型
3）显式修改表中该字段类型为”timestamp“类型

如图所示：

这个小实验，也算小错误吧，感觉还是实战出真知！

参考博客：
1.https://www.cnblogs.com/winkey4986/p/6274729.html
2.https://www.yiibai.com/manual/postgresql/sql-altertable.html

期间，咨询了一下德哥，可以用自定义一个转化函数，把不支持的类型处理一下，exception 转化为你希望的值。大家可以试试！

作者：王雪，西安电子科技大学研究生毕业，现工作于某银行软件研发中心，负责数据库pg后端开发应用，19年11月在合肥pg分享会上认识德哥，开始了与pg之旅。