Menu

PostgreSQL 类型转换:CAST 与 :: 操作符

在 PostgreSQL 中,CAST(value AS type) 和 value::type 都能把值转换成另一种类型。两者是同一种转换,:: 是 PostgreSQL 更短的写法。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

CAST(value AS type) 把一个值转换成另一种类型,value::type 是 PostgreSQL 对同一操作的简写。'42'::integer 和 CAST('42' AS integer) 都返回整数 42。还有第三种形式 type 'literal'(比如 date '2026-11-17'),只能用于常量。

文本 '2026-11-17' 变成了日期,所以加 30 得到 30 天之后的日期。'yes' 是 boolean 接受的写法之一,其他还有 true、t、on 和 1。

把文本转换成数字或日期

从 CSV 文件或表单导入的数据常常是文本。比较或计算之前要先转换,因为按文本比较时,'10' 会排在 '9' 前面:

按整数排序时,9 排在 10 前面,每行的总额分别是 40.5 和 120。首尾的空格是允许的(' 42 '::integer 可以正常工作),但其他任何多余的字符都会报错“invalid input syntax for type integer”。对于其他格式的文本,比如 '17/11/2026' 或 '1,234.50',用 to_date 和 to_number 加上格式模板。

取整和整数除法

把 numeric 转换成整数时,0.5 会向远离零的方向舍入。把 double precision 转换成整数时,0.5 会舍入到偶数。整数除法会丢掉小数部分:

结果依次是 3、-3、2、4、3、3.5 和 12。如果想直接截掉小数而不是四舍五入,就在转换之前用 trunc() 或 floor()。

转换成更小的类型时会检查范围:70000::smallint 会报错“smallint out of range”,而 12.345::numeric(5, 2) 会舍入成 12.35。

转换之前先检查输入

一次转换失败会让整条语句停止。从 PostgreSQL 16 开始,pg_input_is_valid(text, type) 可以检查转换能否成功,pg_input_error_info 返回错误信息而不抛出错误:

只有 '42' 是合法的整数。'2026-02-30' 看起来像日期,其实不是,所以它的 valid_date 也是 false。NULL 那一行的每一列都是 NULL,因为转换 NULL 得到的是新类型的 NULL。

PostgreSQL 什么时候自动转换

带引号的字面量在 PostgreSQL 看到它的使用位置之前是没有类型的。所以 WHERE id = '5' 和 '1' + 1 都能运行,字面量会被当作整数读取。已经有类型的值则不会自动转换。'1'::text + 1 会报错“operator does not exist: text + integer”,拿 text 列和 integer 列比较也会这样。这时要自己加上类型转换。

写入时会宽松一些。如果存在赋值转换,INSERT 或 UPDATE 会把值转换成列的类型,所以把 numeric 12.7 插入整数列,存下来的是 13。

要永久修改列的类型,用 ALTER TABLE ... ALTER COLUMN ... TYPE ... USING 把每一行转换一次:

ALTER TABLE imports ALTER COLUMN qty TYPE integer USING qty::integer;

数据类型页面列出了可以转换成的类型。

常见问题

PostgreSQL 中的 :: 是什么意思?

它是 PostgreSQL 的类型转换操作符:'42'::integer 把文本 '42' 转换成整数。它和标准写法 CAST('42' AS integer) 效果相同。

PostgreSQL 中应该用 CAST 还是 ::?

两者行为一样。:: 更短,在 PostgreSQL 代码中很常见。CAST(... AS ...) 是标准 SQL,MySQL、SQL Server 和 SQLite 也都支持,所以需要可移植的查询就用它。

文本不是数字时,怎么转换才不会报错?

先检查。从 PostgreSQL 16 开始,pg_input_is_valid('4x2', 'integer') 会返回 false 而不是报错,所以 CASE WHEN pg_input_is_valid(s, 'integer') THEN s::integer END 对无效输入返回 NULL。在旧版本中,先用 s ~ '^-?\d+$' 这样的正则表达式检查文本。

为什么 PostgreSQL 中 7 / 2 返回 3?

两个整数相除得到的是整数,小数部分被丢掉。把其中一边转换成 numeric 就能得到 3.5:7::numeric / 2。

Coddy 编程语言插图

和 Coddy 一起学编程

开始