我要评论一、背景说明
在 postgresql 数据库中，随着业务调整，可能需要修改某个字段的数据类型。
例如，某张业务表中有一个价格或数值字段，原来的类型是：
double precision
现在需要将其修改为：
bigint
也就是从浮点数类型改为大整数类型。
本文以一张示例表为例，介绍如何在 postgresql 中修改字段类型，并同步处理字段默认值。
二、示例表结构
假设有一张表：
public.example_price_change_log
其中有一个字段：
new_value
原始类型为：
double precision
可以通过 postgresql 的 \d+ 命令查看表结构：
\d+ public.example_price_change_log
示例结构如下：
table "public.example_price_change_log" column | type --------------------+------------------ id | integer operator_id | integer category_code | text new_value | double precision effective_time | bigint created_time | bigint expire_time | bigint status | integer remark | text
其中，本文重点修改字段：
new_value
三、修改字段类型的 sql
postgresql 中修改字段类型使用：
alter table ... alter column ... type
本次将 new_value 从 double precision 修改为 bigint，sql 如下：
alter table public.example_price_change_log alter column new_value type bigint using new_value::bigint;
其中：
using new_value::bigint
表示在修改字段类型时，将原字段值强制转换为 bigint 类型。
如果不加 using，在某些类型转换场景下可能会报错，因此建议明确指定转换方式。
四、同步修改默认值
如果原字段存在默认值，例如：
'-1'::integer
字段类型改为 bigint 后，建议将默认值也统一调整为 bigint 类型：
alter table public.example_price_change_log alter column new_value set default -1::bigint;
也可以将字段类型修改和默认值修改合并成一条 sql：
alter table public.example_price_change_log alter column new_value type bigint using new_value::bigint, alter column new_value set default -1::bigint;
五、修改后的表结构
执行：
\d+ public.example_price_change_log
可以看到字段已经变为：
column | type | default ----------+--------+-------------- new_value | bigint | - 1::bigint
这里需要注意，postgresql 在显示默认值表达式时，可能会把：
-1::bigint
显示为：
- 1::bigint
中间多了一个空格。
这是 postgresql 对表达式的显示格式，不影响实际含义。
也就是说：
- 1::bigint
和：
-1::bigint
是等价的。
六、验证字段默认值
可以通过 information_schema.columns 查看字段默认值：
select column_default from information_schema.columns where table_schema = 'public' and table_name = 'example_price_change_log' and column_name = 'new_value';
查询结果可能类似：
- 1::bigint
这是正常现象，不需要额外处理。
七、注意事项
1. 修改前建议检查是否存在小数
因为原字段类型是：
double precision
可能存在小数数据。
在转换为 bigint 前，可以先检查是否有小数：
select * from public.example_price_change_log where new_value is not null and new_value <> trunc(new_value);
如果查询结果为空，说明该字段中不存在小数，可以相对安全地转换。
2. 默认转换可能会产生取整行为
使用下面这种方式转换：
using new_value::bigint
postgresql 会按照自身规则将浮点数转换为整数。
例如：
12.3 -> 12 12.7 -> 13
如果你的业务不允许自动四舍五入，需要提前确认转换规则。
3. 如果希望直接截断小数
如果希望小数部分直接截断，可以使用 trunc() 函数：
alter table public.example_price_change_log alter column new_value type bigint using trunc(new_value)::bigint, alter column new_value set default -1::bigint;
转换效果类似：
12.9 -> 12 12.1 -> 12
4. 生产环境建议先备份
修改字段类型属于表结构变更，生产环境执行前建议先备份数据。
可以先创建一张备份表：
create table public.example_price_change_log_bak as select * from public.example_price_change_log;
也可以使用 postgresql 自带的备份工具，例如：
pg_dump
八、完整 sql 示例
方式一：普通转换
alter table public.example_price_change_log alter column new_value type bigint using new_value::bigint, alter column new_value set default -1::bigint;
方式二：截断小数后转换
alter table public.example_price_change_log alter column new_value type bigint using trunc(new_value)::bigint, alter column new_value set default -1::bigint;
九、最终效果
修改完成后，字段类型从：
double precision
变为：
bigint
字段默认值为：
-1::bigint
如果在 \d+ 中显示为：
- 1::bigint
也是正常的，只是 postgresql 的显示格式问题。
十、总结
postgresql 修改字段类型的核心语法是：
alter table 表名 alter column 字段名 type 新类型 using 字段名::新类型;
本案例中，将字段从 double precision 修改为 bigint 的示例 sql 为：
alter table public.example_price_change_log alter column new_value type bigint using new_value::bigint;
如果字段有默认值，建议同步修改默认值类型：
alter table public.example_price_change_log alter column new_value set default -1::bigint;
最终推荐写法：
alter table public.example_price_change_log alter column new_value type bigint using new_value::bigint, alter column new_value set default -1::bigint;
这样可以保证字段类型和默认值类型保持一致，避免后续维护时产生歧义。
以上就是postgresql修改字段类型之将double precision改为bigint的方法步骤的详细内容，更多关于postgresql修改字段类型的资料请关注代码网其它相关文章！
发表评论