PostgreSQL修改字段类型之将double precision改为BIGINT的方法步骤_MsSqlserver

一、背景说明

在 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修改字段类型的资料请关注代码网其它相关文章！