PostgreSQL / 系统课时
类型转换与字段选择
类型转换与字段选择
字段类型选错会带来精度损失、比较异常和索引失效。本课将通过一张支付记录表演示显式转换,并给出按金额、时间、计数和标识语义选择字段类型的方法。
学习目标
- 使用 PostgreSQL 显式类型转换语法
- 理解隐式转换的便利与风险
- 按业务语义选择常见字段类型
显式转换
PostgreSQL 支持 value::type 和 CAST(value AS type) 两种写法。显式转换能清楚表达输入目标类型,也能在接口入库前尽早暴露非法数据。
下面把文本形式的金额、时间、次数和 UUID 转为目标字段类型。
sql
CREATE TEMP TABLE payment_records (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
paid_at timestamptz NOT NULL,
retry_count smallint NOT NULL DEFAULT 0,
external_id uuid NOT NULL UNIQUE
);
INSERT INTO payment_records (
amount,
paid_at,
retry_count,
external_id
)
VALUES (
'199.90'::numeric(12, 2),
'2026-07-29 10:00:00+08'::timestamptz,
'2'::smallint,
'550e8400-e29b-41d4-a716-446655440000'::uuid
);
SELECT
amount,
pg_typeof(amount) AS amount_type,
paid_at AT TIME ZONE 'Asia/Kuala_Lumpur' AS local_time,
retry_count
FROM payment_records;预期结果
text
amount | amount_type | local_time | retry_count
--------+-------------+---------------------+------------
199.90 | numeric | 2026-07-29 10:00:00 | 2这里的时间值带有 +08 时区偏移,转换到 Asia/Kuala_Lumpur 后仍显示同一当地时间。
隐式转换的风险
数据库会在部分表达式中自动转换类型,例如把整数常量转换为更宽的数值类型。但隐式转换并非任意类型之间都存在,字符串内容不合法时仍会报错。
查询条件两侧类型不一致时,数据库可能转换列值而不是参数值,从而增加计算成本或影响索引使用。应用层应尽量使用与字段一致的参数类型,不要长期依赖文本到数字、文本到时间的自动猜测。
按业务语义选择字段
金额应优先使用 numeric(精度, 小数位),避免浮点类型带来的二进制舍入误差。计数和状态码应根据范围选择 smallint、integer 或 bigint,不要为了省事全部使用文本。
只表示日期时使用 date;表示时间点且涉及多时区时使用 timestamptz。固定长度并不会自动带来性能优势,普通可变文本通常使用 text 即可。
外部全局标识适合 uuid,数据库内部高频递增主键常用 bigint identity。布尔语义应使用 boolean,不要用 'Y'、'N' 或 0、1 混合表达。
转换失败的处理
像 'abc'::integer 这样的转换会直接报错。批量导入前应先清洗数据,或通过正则、范围检查和临时表分阶段转换,避免一条坏数据让整批事务回滚。
按自己的节奏,确认真正理解。