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 这样的转换会直接报错。批量导入前应先清洗数据,或通过正则、范围检查和临时表分阶段转换,避免一条坏数据让整批事务回滚。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

类型转换与字段选择

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通