PostgreSQL Type Casts 类型转换实用教程

显式类型转换的两种写法

类型转换（cast）明确告诉 PostgreSQL 按目标数据类型解释或转换一个值。推荐在类型不明确、运算符有歧义或接口参数类型不匹配时显式转换。

表达式::目标类型
CAST(表达式 AS 目标类型)

SELECT '42'::integer;
SELECT CAST('42' AS integer);
-- 常见简写：把字符串转成日期
SELECT '2026-10-20'::date;
-- 指定 numeric 精度和小数位数
SELECT 12.345::numeric(8, 2);
SELECT CAST(12.345 AS numeric(8, 2));

:: 是 PostgreSQL 常用的简写；CAST(... AS ...) 是标准 SQL 写法。两者都可用于查询表达式，也可用于插入、更新等语句中的值。

常见类型转换示例

转换	示例	注意事项
文本 → 整数	`'123'::integer`	文本必须是可解析的整数，越界或格式错误会报错。
整数 → 文本	`123::text`	适合拼接或展示；不要把文本排序误认为数值排序。
文本 → 数值	`'19.95'::numeric`	金额等十进制数据通常优先使用 `numeric`。
文本 → 日期	`'2026-10-20'::date`	推荐使用无歧义的 ISO 格式。
整数 → 布尔	不应依赖此类转换	PostgreSQL 不会把任意整数自动当作布尔值；请传 `true`/`false`。

SELECT '100'::integer + 23 AS total, 123::text AS text_value, '19.95'::numeric AS amount, '2026-10-20'::date AS business_date, 'true'::boolean AS enabled;

隐式转换与类型推断

PostgreSQL 在部分运算、赋值和函数调用中会自动选择兼容类型，但并非所有类型都能隐式转换。具体能否自动转换取决于上下文和运算符、函数的参数类型。

-- 明确转换，避免依赖上下文推断
SELECT 10::numeric / 3;
-- 整数除法会丢弃小数部分
SELECT 10 / 3;
-- 3
-- 文本拼接时显式转换更清楚
SELECT 'order-' || 123::text;

注意

注意：整数除法会得到整数结果；如果需要小数，应在除法前把至少一个操作数转成 numeric 或适当的浮点类型。不要等结果已经被截断后再转换。

对于 CASE、UNION、VALUES 等包含多个分支或行的表达式，PostgreSQL 需要为结果确定统一类型。遇到类型不匹配时，可在各分支显式转换为同一类型。

SELECT CASE WHEN is_paid THEN amount::numeric ELSE 0::numeric END AS displayed_amount FROM invoices;

数值精度、舍入与溢出

转换为有限精度的 numeric(p, s) 时，小数位数会按目标 scale 进行舍入；超出可容纳范围时会报错。把 numeric 转为整数时也会舍入，而不是简单截去小数。

SELECT 12.345::numeric(8, 2);
-- 12.35
SELECT 12.7::integer;
-- 13
-- 明确截断小数部分时使用函数表达意图
SELECT trunc(12.7)::integer;
-- 12

信息

金额运算通常使用 numeric，避免二进制浮点近似带来的精度问题。先确定业务需要的舍入规则，再选择 round、trunc 等函数；数据库转换规则不一定等同于业务规则。

日期、时间与时区转换

文本转日期或时间时，优先使用 ISO 8601 格式。解析本地化日期字符串可能受 DateStyle 等会话设置影响。

SELECT '2026-10-20'::date;
SELECT '2026-10-20 09:30:00'::timestamp;
SELECT '2026-10-20 09:30:00+08'::timestamptz;
-- 用 AT TIME ZONE 明确指定时区语义
SELECT timestamp '2026-10-20 09:30:00' AT TIME ZONE 'Asia/Shanghai';

注意

timestamp（不带时区）保存的是日期和时间字段，不代表某个确定时区的时刻；timestamptz 表示时间点，输入和输出会结合时区处理。生产系统应明确应用时区，并避免仅靠隐式转换猜测时间含义。

AT TIME ZONE 的结果类型取决于输入：对 timestamp without time zone 使用时，它按指定时区解释本地时间并返回 timestamptz；对 timestamptz 使用时，它返回指定时区的本地 timestamp。

NULL 的类型转换

NULL 本身不携带具体值，某些表达式中 PostgreSQL 可能无法推断其类型。此时给 NULL 显式指定类型，或通过目标列类型确定类型。

SELECT NULL::integer;
-- UNION 两侧显式使用相同类型
SELECT 1::integer AS value UNION ALL SELECT NULL::integer;
--
INSERT 也可以明确表示空值的目标类型 INSERT INTO users (id, nickname) VALUES (10, NULL::text);

类型转换不会改变 NULL 的语义：转换后的值仍然是 NULL。判断空值仍要使用 IS NULL 或 IS NOT NULL，不能使用 = NULL。

类型转换与索引

在索引列上套用转换函数或转换到其他类型，可能让普通索引无法直接用于该条件。若输入值可转换，通常优先转换参数，让被索引列保持原样。

-- 假设 created_at 是 timestamptz，明确转换查询边界
SELECT id, created_at FROM events WHERE created_at >= '2026-10-20 00:00:00+08'::timestamptz;
-- 不要无故对每行的索引列转换类型
SELECT id FROM events WHERE created_at::date = '2026-10-20'::date;

信息

是否使用索引最终由执行计划决定。若查询确实需要按转换后的表达式过滤，可考虑匹配该表达式的表达式索引，并通过 EXPLAIN 检查计划；不要仅凭语法推断性能。

转换失败与安全处理

转换失败会产生错误，例如非法数字、超出范围或无效日期。PostgreSQL 没有通用的内置 TRY_CAST 可以让任意失败转换自动返回 NULL；不能假设 NULLIF 可以拦截所有格式错误。

-- 非法文本会导致转换错误
SELECT '12x'::integer;
-- 对受限格式的文本，先验证再转换
SELECT CASE WHEN raw_value ~ '^[0-9]+$' THEN raw_value::integer ELSE NULL END AS parsed_value FROM imported_data;

注意

格式验证必须与目标类型和业务范围相符。正则只能验证示例中的简单非负整数形式，不能作为任意日期、数字格式的通用解析器；正式导入流程应记录无效数据并明确处理策略，而不是静默丢弃错误。

对于复杂格式，应在导入前清洗数据，或使用经过验证的解析逻辑；需要兼顾范围时，还要检查目标类型上限，避免合法格式但数值越界。

实用检查清单

表达式类型有歧义时使用 :: 或 CAST(... AS ...) 明确标注。
数值运算先确定精度与舍入规则；避免意外整数除法。
日期时间使用无歧义格式，并区分 timestamp 与 timestamptz。
不要依赖未验证的用户输入直接转换；非法值会报错并中断语句。
查询索引列时避免不必要的列侧转换，结合 EXPLAIN 验证计划。
在 CASE、UNION 等多分支表达式中，必要时统一各分支的类型。

显式类型转换的两种写法​

常见类型转换示例​

隐式转换与类型推断​

数值精度、舍入与溢出​

日期、时间与时区转换​

NULL 的类型转换​

类型转换与索引​

转换失败与安全处理​

实用检查清单​