PostgreSQL修改字段类型之将doubleprecision改成BIGINT的方法步骤

一、背景说明

在 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                  | integeroperator_id         | integercategory_code       | textnew_value           | double precisioneffective_time      | bigintcreated_time        | bigintexpire_time         | bigintstatus              | integerremark              | text

其中，本文重点修改字段：

new_value

三、修改字段类型的 SQL

PostgreSQL 中修改字段类型使用：

ALTER TABLE ... ALTER COLUMN ... TYPE

本次将 new_value 从 double precision 修改为 BIGINT，SQL 如下：

ALTER TABLE public.example_price_change_logALTER COLUMN new_value TYPE BIGINTUSING new_value::BIGINT;

其中：

USING new_value::BIGINT

表示在修改字段类型时，将原字段值强制转换为 BIGINT 类型。

如果不加 USING，在某些类型转换场景下可能会报错，因此建议明确指定转换方式。

四、同步修改默认值

如果原字段存在默认值，例如：

'-1'::integer

字段类型改为 BIGINT 后，建议将默认值也统一调整为 BIGINT 类型：

ALTER TABLE public.example_price_change_logALTER COLUMN new_value SET DEFAULT -1::BIGINT;

也可以将字段类型修改和默认值修改合并成一条 SQL：

ALTER TABLE public.example_price_change_logALTER COLUMN new_value TYPE BIGINTUSING 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_defaultFROM information_schema.columnsWHERE 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_logWHERE new_value IS NOT NULL  AND new_value <> trunc(new_value);

如果查询结果为空，说明该字段中不存在小数，可以相对安全地转换。

2. 默认转换可能会产生取整行为

使用下面这种方式转换：

USING new_value::BIGINT

PostgreSQL 会按照自身规则将浮点数转换为整数。

例如：

12.3 -> 1212.7 -> 13

如果你的业务不允许自动四舍五入，需要提前确认转换规则。

3. 如果希望直接截断小数

如果希望小数部分直接截断，可以使用 trunc() 函数：

ALTER TABLE public.example_price_change_logALTER COLUMN new_value TYPE BIGINTUSING trunc(new_value)::BIGINT,ALTER COLUMN new_value SET DEFAULT -1::BIGINT;

转换效果类似：

12.9 -> 1212.1 -> 12

4. 生产环境建议先备份

修改字段类型属于表结构变更，生产环境执行前建议先备份数据。

可以先创建一张备份表：

CREATE TABLE public.example_price_change_log_bak ASSELECT *FROM public.example_price_change_log;

也可以使用 PostgreSQL 自带的备份工具，例如：

pg_dump

八、完整 SQL 示例

方式一：普通转换

ALTER TABLE public.example_price_change_logALTER COLUMN new_value TYPE BIGINTUSING new_value::BIGINT,ALTER COLUMN new_value SET DEFAULT -1::BIGINT;

方式二：截断小数后转换

ALTER TABLE public.example_price_change_logALTER COLUMN new_value TYPE BIGINTUSING 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_logALTER COLUMN new_value TYPE BIGINTUSING new_value::BIGINT;

如果字段有默认值，建议同步修改默认值类型：

ALTER TABLE public.example_price_change_logALTER COLUMN new_value SET DEFAULT -1::BIGINT;

最终推荐写法：

ALTER TABLE public.example_price_change_logALTER COLUMN new_value TYPE BIGINTUSING new_value::BIGINT,ALTER COLUMN new_value SET DEFAULT -1::BIGINT;

这样可以保证字段类型和默认值类型保持一致，避免后续维护时产生歧义。

以上就是PostgreSQL修改字段类型之将double precision改为BIGINT的方法步骤的详细内容，更多关于PostgreSQL修改字段类型的资料请关注本站其它相关文章！

您可能感兴趣的文章:

• PostgreSQL日期时间字段类型选择指南
• Java postgresql数组字段类型处理方法详解
• postgresql 实现获取所有表名,字段名,字段类型,注释
• PostgreSQL 中字段类型varchar的用法