PHP小码农

Appearance

类型转换（CAST） ​

PostgreSQL 是强类型数据库：1 + '1' 不会自动相加，'abc'::integer 会直接报错。类型转换（CAST）就是在这层类型墙上开出的受控通道——它规定了「哪些类型之间可以转换、在什么场景下可以自动转换、转换时具体执行哪个函数」。

理解 CAST 的价值不在于会写 ::，而在于三件事：看懂报错（为什么会提示 operator does not exist）、写对索引（为什么 WHERE created_at::date = '2026-01-01' 用不上索引）、控制隐式转换（为什么 CREATE CAST 能让自定义类型融入内置运算体系）。

前置知识

阅读本节前，建议先了解：域类型（Domain）

基础概念 ​

为什么需要类型转换 ​

数据库里的「同一个值」可以有多种类型表示：

值可能的类型
123integer / bigint / numeric / text / double precision
'2026-09-22'date / timestamp / timestamptz / text
'1 day'interval / text
'{1,2,3}'integer[] / text[] / jsonb

当两个不同类型要参与同一个运算或赋值时，PostgreSQL 必须做一次「类型对齐」。对齐的方式有两种：

方式谁决定例子
隐式转换（Implicit Cast）数据库自动SELECT 1 + 1.5; → integer 提升为 numeric
显式转换（Explicit Cast）你必须写出来SELECT '123'::integer;

三种转换上下文 ​

这是理解 CAST 的核心概念。PostgreSQL 把类型转换分成三个上下文（Cast Context），决定了转换能"多自动"：

上下文触发时机自动程度典型例子
隐式（implicit）任何表达式求值均可触发最高，你敢写它就敢转integer → bigint（函数参数匹配时）
赋值（assignment）只在 INSERT / UPDATE / 赋值的场合中等text → integer（插入时允许）
显式（explicit）必须写 :: 或 CAST()最低text → integer（查询中必须显式）

这句口诀要记住：隐式是"我帮你转"，赋值是"存的时候帮你转"，显式是"你不写我就不转"。

为什么 INSERT 时能写字符串而查询时不行？因为 INSERT INTO t (n) VALUES ('123') 走的是赋值上下文，'123' 是未知类型字面量（unknown literal），会被推断为目标列类型；而 SELECT '123' > 100 里 '123' 是 text，text > integer 没有操作符，直接报错。

sql
-- 赋值上下文：可以
CREATE TEMP TABLE t_int (n integer);
INSERT INTO t_int VALUES ('123');     -- OK，字面量被推断为 integer

-- 查询上下文：不行
SELECT '123' > 100;
text
ERROR:  operator does not exist: text > integer
LINE 1: SELECT '123' > 100;
               ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

两种转换语法 ​

语法示例特点
双冒号（PostgreSQL 扩展）expr::type最简洁，日常首选
函数式（SQL 标准）CAST(expr AS type)可移植性好，语义显式
函数式（PostgreSQL 特有）type(expr)部分类型可用，如 int4('123')，可读性差且不通用

三者等价：

sql
SELECT '123'::integer AS a,
       CAST('123' AS integer) AS b,
       int4('123') AS c;
text
 a  | b  | c
----+----+----
 123 | 123 | 123
(1 row)

建议：业务 SQL 里统一用 ::（简洁），需要跨库迁移的 SQL 用 CAST(... AS ...)。

转换链式写法 ​

:: 可以链式串联，按从左到右顺序执行：

sql
SELECT '2026-09-22 15:30:00'::timestamptz::date::text AS d;
text
     d
------------
 2026-09-22
(1 row)

链式转换要小心精度丢失：timestamptz → date 丢掉时分秒，再转回 timestamp 也回不来了。链式转换写长了容易埋 bug。

未知类型字面量：unknown ​

有一个特殊"类型"叫 unknown，所有不加修饰的字符串字面量都是它。它的行为是"跟随上下文"：

场景'2026-01-01' 的实际类型原因
WHERE d = '2026-01-01'，d 是 datedate推断为对端类型
SELECT '2026-01-01'text无上下文，退化为 text
SELECT '2026-01-01'::datedate显式指定
SELECT date '2026-01-01'date类型前缀字面量
sql
SELECT pg_typeof('123') AS unknown_literal,
       pg_typeof('123'::int) AS cast_literal,
       pg_typeof(123) AS int_literal;
text
 unknown_literal | cast_literal | int_literal
-----------------+--------------+-------------
 unknown         | integer      | integer
(3 rows)

另外还有类型前缀字面量语法，比 :: 更早被解析，写日期时很常用：

sql
SELECT date '2026-09-22'   AS d,
       timestamp '2026-09-22 15:30:00' AS ts,
       interval '2 hours'  AS iv,
       bytea '\xDEADBEEF'  AS b;

与 CAST 相邻的概念辨析 ​

概念含义是否改变类型
CAST类型转换本身是
类型提升（Type Promotion）数值运算时自动选"更宽"的类型是（隐式 CAST 的一种）
类型推断（Type Inference）编译器根据上下文猜类型可能不转
序列化（Serialization）把值转成字节流/文本是（另有专门机制）
强制类型（Coerce）官方文档里对"自动 CAST"的统称是

语法与用法 ​

显式转换的写法 ​

sql
-- 基本写法
SELECT '42'::integer;

-- 标准写法
SELECT CAST('42' AS integer);

-- 在列上使用
SELECT id, created_at::date AS d FROM orders;

-- 在函数参数上传入
SELECT to_char(now()::date, 'YYYY-MM-DD');

-- 在 INSERT 中
INSERT INTO stats (day, cnt) VALUES (now()::date, '100'::int);

常用转换速查表 ​

目标类型写法说明
整数'42'::integer / ::int4 / ::bigint字符串必须全是数字，否则报错
小数'3.14'::numeric(5,2) / ::float8numeric 精确，float8 有精度损失
文本123::text / 123::varchar(10)几乎所有类型都能转 text
布尔'true'::boolean / '1'::boolean / 't'::boolean接受 true/t/yes/on/1 等多种写法
日期'2026-09-22'::date也接受 2026/09/22、20260922
时间戳'2026-09-22 15:30'::timestamp缺秒能补
时区时间戳'2026-09-22 15:30+08'::timestamptz建议显式带时区
间隔'90 minutes'::interval / 90::text::interval直接转整数不行
JSON'{"a":1}'::jsonb非法 JSON 会报错
数组'{1,2,3}'::int[]元素类型必须匹配
UUID'550e8400-e29b-41d4-a716-446655440000'::uuid格式必须合法
网段'192.168.1.0/24'::cidr主机位必须为 0
枚举'paid'::order_status值必须在枚举里

查询系统里已注册的转换规则 ​

pg_cast 目录记录了所有转换路径，是排查"为什么转不了"的一手资料：

sql
SELECT castsource::regtype AS source,
       casttarget::regtype AS target,
       castfunc::regproc   AS cast_function,
       castcontext         AS context,
       castmethod          AS method
FROM pg_cast
WHERE castsource::regtype IN ('text'::regtype, 'integer'::regtype)
ORDER BY source::text, target::text, context
LIMIT 20;

输出的关键列含义：

列含义
castsource源类型
casttarget目标类型
castfunc执行转换的函数；0 表示"二进制兼容，无需函数"
castcontexti = implicit，a = assignment，e = explicit
castmethodf = 用函数，i = I/O 转换，b = 二进制强转

按上下文分类统计：

sql
SELECT castcontext,
       CASE castcontext
         WHEN 'i' THEN 'implicit（隐式）'
         WHEN 'a' THEN 'assignment（赋值）'
         WHEN 'e' THEN 'explicit（显式）'
       END AS meaning,
       count(*) AS rules
FROM pg_cast
GROUP BY castcontext
ORDER BY castcontext;
text
 castcontext |         meaning         | rules
-------------+-------------------------+-------
 a           | assignment（赋值）       |    46
 e           | explicit（显式）         |   138
 i           | implicit（隐式）         |    43
(3 rows)

数值类型的隐式提升阶梯 ​

数值类型之间有明确的"由窄到宽"提升方向，窄转宽是隐式的，宽转窄必须显式：

sql
SELECT 1::smallint + 1::integer AS r1,      -- smallint → integer，隐式
       pg_typeof(1::smallint + 1::integer) AS t1,
       pg_typeof(1::integer + 1::bigint) AS t2,
       pg_typeof(1::integer + 1::numeric) AS t3,
       pg_typeof(1::integer + 1::double precision) AS t4;
text
 r1 |    t1    |   t2   |   t3    |    t4
----+----------+--------+---------+-----------
  2 | integer  | bigint | numeric | double precision
(1 row)

提升阶梯：

text
smallint → integer → bigint → numeric → double precision

记忆要点：整数族可以自动变宽，但只有 numeric 和 float 能自动接纳整数。反过来 bigint 转 integer 会报错：

sql
SELECT 1::bigint + 1::integer;    -- OK
SELECT 1::integer + 1::bigint;    -- OK（小的自动变宽）
text
SELECT '9999999999'::bigint::integer;
text
ERROR:  integer out of range

这个报错是运行时才触发的——类型转换合法，值超范围了。要安全转换用 CASE 或 numeric 中转。

文本与其他类型：text 是"万能中转站" ​

text 能接受几乎所有类型的显式转换，因为每个类型都有输出函数（output function）。但text 转别的类型走的是输入函数，格式必须严格匹配：

sql
-- 各种类型 → text（总是可行）
SELECT 123::text, 3.14::text, true::text, now()::text, '{"a":1}'::jsonb::text;
text
  123 |    3.14 | true |               now               |      text
------+---------+------+-------------------------------+--------------
 123  | 3.14    | true | 2026-09-22 15:30:00.123456+08 | {"a": 1}
(1 row)
sql
-- text → 其他类型：格式不对就报错
SELECT 'abc'::integer;
text
ERROR:  invalid input syntax for type integer: "abc"
LINE 1: SELECT 'abc'::integer;
               ^
sql
SELECT '3.14'::integer;   -- 小数转整数也不允许
text
ERROR:  invalid input syntax for type integer: "3.14"
sql
-- 正确做法：先转 numeric 再取整
SELECT round('3.14'::numeric)::integer AS rounded,      -- 3
       floor('3.14'::numeric)::integer AS floored,      -- 3
       trunc('3.14'::numeric)::integer AS truncated;    -- 3
text
 rounded | floored | truncated
---------+---------+-----------
       3 |       3 |         3
(1 row)

空字符串与 NULL 的转换陷阱 ​

sql
SELECT ''::integer;
text
ERROR:  invalid input syntax for type integer: ""

但 NULL 转任何类型都是 NULL：

sql
SELECT NULL::integer AS a, NULL::date AS b, pg_typeof(NULL::date) AS t;
text
 a | b |    t
---+---+----------
   |   | date
(1 row)

这带来一个真实场景的坑：应用层传来的空字符串 '' 转整数会炸，而 NULL 不会。所以在 ETL 里经常要写：

sql
-- 安全转换：空串当 NULL
SELECT NULLIF(trim(raw_val), '')::integer AS n FROM raw_table;

对于格式完全不确定的输入，PostgreSQL 本身不提供 TRY_CAST（那是 SQL Server 的语法），需要自己写函数或先过滤：

sql
-- 过滤掉非法值
SELECT raw_val::integer
FROM raw_table
WHERE raw_val ~ '^\d+$';

通过 I/O 转换（WITH INOUT） ​

没有专门转换函数的类型对，PostgreSQL 会走"先转成文本再解析"的路径，叫 I/O 转换（castmethod = 'i'）：

sql
-- 没有专门的 cast 函数，但能转：底层是 text → 解析为 jsonb
SELECT '{"a":1}'::jsonb;
sql
-- 看看它用了什么转换方法
SELECT castsource::regtype AS source,
       casttarget::regtype AS target,
       castmethod          AS method
FROM pg_cast
WHERE castsource::regtype = 'json'::regtype
  AND casttarget::regtype = 'jsonb'::regtype;
text
 source | target | method
--------+--------+--------
 json   | jsonb  | i
(1 row)

method = 'i' 意味着它靠 json 的输出函数 + jsonb 的输入函数完成，性能比专用函数略差但可用。

自定义类型转换：CREATE CAST ​

当你有自定义类型，且希望它融入内置运算体系时，可以注册转换规则。

三种写法：

写法语法适用场景
带函数CREATE CAST (a AS b) WITH FUNCTION f(a)需要真正的转换逻辑
走 I/OCREATE CAST (a AS b) WITH INOUT两个类型都有文本 I/O，且文本形式可互解析
二进制强转CREATE CAST (a AS b) WITHOUT FUNCTION两个类型二进制表示完全相同

WITHOUT FUNCTION 是最危险的（它不校验任何东西，直接改标类型），也是最快的。它只允许用于存储表示完全一致的类型对，例如：

sql
-- 假设我们有个和 text 二进制兼容的自定义类型
CREATE DOMAIN brand AS text;
CREATE CAST (text AS brand) WITHOUT FUNCTION AS IMPLICIT;

如果不满足二进制兼容，PostgreSQL 会直接拒绝：

text
ERROR:  source and target data types are not physically compatible

三种转换上下文的写法：

sql
CREATE CAST (a AS b) WITH FUNCTION f(a) AS ASSIGNMENT;
CREATE CAST (a AS b) WITH FUNCTION f(a) AS IMPLICIT;
CREATE CAST (a AS b) WITH FUNCTION f(a);   -- 默认，仅显式
上下文关键字效果
显式（默认，不写）只有 :: 才能触发
赋值AS ASSIGNMENTINSERT / UPDATE / 函数参数传值可触发
隐式AS IMPLICIT任何表达式都能触发，影响面最大

删除自定义转换 ​

sql
DROP CAST (text AS brand);
DROP CAST IF EXISTS (text AS brand);

注意 DROP CAST 必须写全源类型和目标类型，没有"按名字删"的形式。

详细说明 ​

为什么隐式转换要慎用 ​

AS IMPLICIT 看起来很方便——写完自定义类型就能直接和内置类型运算了。但它的代价是可能破坏重载函数解析与操作符选择。

一个经典的坑：

sql
-- 假设给 brand 类型加了隐式转换到 text
CREATE CAST (brand AS text) WITH INOUT AS IMPLICIT;

-- 现在这些查询都可能出现"歧义"
SELECT 'a' || 'b'::brand;   -- 走 text 拼接，还是报错？

如果系统里同时存在两个候选操作符，PostgreSQL 会报：

text
ERROR:  operator is not unique: text || brand
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

三条经验法则：

  1. 默认选择显式（不写上下文）。 只有确实需要时才升级到 assignment。
  2. AS IMPLICIT 只在源类型"本就是这个概念的窄化形式"时使用。 例如自定义的 positive_int 隐式转 integer 是合理的（它本质是 integer 加约束），而 jsonb 隐式转 text 就不合理。
  3. 检查升级影响。 加隐式转换前，先在同一实例上跑一遍完整回归测试，尤其是有大量重载函数和操作符的库。

索引与隐式转换：最实用的排查技能 ​

类型转换最常见的性能事故是在索引列上做转换，导致索引失效：

sql
-- 假设 created_at 是 timestamptz 且建了 btree 索引
CREATE INDEX idx_orders_created ON orders (created_at);

-- 慢查询：列被转换了，索引用不上
EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE created_at::date = '2026-09-22';
text
                 QUERY PLAN
--------------------------------------------
 Seq Scan on orders
   Filter: ((created_at)::date = '2026-09-22'::date)
(2 rows)

改成范围条件（右侧常量而非左侧列），索引立刻生效：

sql
EXPLAIN (COSTS OFF)
SELECT * FROM orders
WHERE created_at >= '2026-09-22'::date
  AND created_at <  '2026-09-23'::date;
text
                             QUERY PLAN
---------------------------------------------------------------------
 Index Scan using idx_orders_created on orders
   Index Cond: ((created_at >= '2026-09-22'::date) AND (created_at < '2026-09-23'::date))
(2 rows)

如果业务上确实必须按 ::date 过滤，还有第二条路：建表达式索引。

sql
CREATE INDEX idx_orders_created_date ON orders ((created_at::date));

EXPLAIN (COSTS OFF)
SELECT * FROM orders WHERE created_at::date = '2026-09-22';
text
                          QUERY PLAN
---------------------------------------------------------------
 Index Scan using idx_orders_created_date on orders
   Index Cond: ((created_at)::date = '2026-09-22'::date)
(2 rows)

表达式必须逐字照抄才能匹配索引，created_at::date 和 CAST(created_at AS date) 在规划器看来是同一个表达式（都是 date(created_at)），但 created_at::date + 0 就不是了。

类型转换的方向性 ​

类型转换通常是单向的。看这张对照表：

转换对隐式赋值显式
integer → bigint可可可
bigint → integer不可不可可
text → integer不可可可
integer → text不可可可
date → timestamp可可可
timestamp → date不可可可
integer → boolean不可不可可
boolean → integer不可不可可
json → jsonb不可可可
varchar → text可可可
text → varchar不可可可

规律总结：

  1. 无损的宽化转换（narrowing → widening）倾向于隐式。
  2. 有损或可能失败的转换只能显式。
  3. 同族无长度限制的（varchar → text）可以隐式。

转换失败的排查流程 ​

遇到 cannot cast type X to Y 时，按这个顺序排查：

第一步：确认是否只是写法问题。

sql
-- 报错：cannot cast type date to integer
SELECT now()::date::integer;

日期转整数没有注册路径，但先转 text 再转 integer 就行（虽然是脏做法）：

sql
SELECT (now()::date::text)::integer;    -- 得到 20260922

真正合理的做法是自己的函数：

sql
CREATE OR REPLACE FUNCTION date_to_int(d date) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT to_char(d, 'YYYYMMDD')::integer;
$$;

SELECT date_to_int('2026-09-22'::date);   -- 20260922

第二步：查系统目录确认是否存在转换路径。

sql
SELECT count(*) AS rules
FROM pg_cast
WHERE castsource = 'date'::regtype AND casttarget = 'integer'::regtype;
text
 rules
-------
     0
(1 row)

第三步：查是否有可用的中间类型。

sql
-- date 能转到哪些类型？
SELECT casttarget::regtype AS can_cast_to, castcontext
FROM pg_cast
WHERE castsource = 'date'::regtype
ORDER BY casttarget::regtype::text;
text
 can_cast_to  | castcontext
--------------+-------------
 date         | i
 timestamp    | e
 timestamptz  | i
(3 rows)

所以 date → text 也不在 pg_cast 列表里？其实每个类型转 text 都是内置保证的，不走 pg_cast，这在文档里描述为"所有类型都能转 text 和从 text 转（只要 I/O 支持）"。这也解释了为什么"先转 text 再转目标类型"几乎总能成功——代价是解析开销与潜在的格式不规范。

为什么推荐 EXPLAIN 配合排查 ​

类型不匹配往往不报错而是"静默变慢"。一个标准动作：

sql
-- 先看清楚两边的类型
SELECT pg_typeof(created_at) AS col_type,
       pg_typeof('2026-09-22') AS lit_type
FROM orders LIMIT 1;
text
  col_type   |  lit_type
-------------+------------
 timestamptz | unknown
(1 row)

unknown 是好事：它说明字面量会跟着列类型走，不会产生多余的转换。但如果字面量已经被显式转成了 text 或 date，就可能引入转换。

实战示例 ​

案例一：导入脏数据时的安全转换 ​

目标：从一张全 text 的临时表导入，处理空串、非法格式、超范围三类问题。

步骤 1：造一批"脏"原始数据

sql
DROP TABLE IF EXISTS raw_import, clean_users CASCADE;

CREATE TABLE raw_import (
  id     text,
  name   text,
  age    text,
  salary text,
  joined text,
  active text
);

INSERT INTO raw_import VALUES
  ('1',  '张三', '28',   '15000.50', '2026-01-15', 'true'),
  ('2',  '李四', '35',   '22000',    '2026-03-02', 't'),
  ('3',  '王五', '',     '18000.75', '2026-05-20', '1'),
  ('4',  '赵六', '  42 ', '',        '2026/07/08', 'yes'),
  ('5',  '孙七', 'abc',  'not-a-num','2026-08-30', 'false'),
  ('6',  '周八', '150',  '99999999999.99', '2026-09-01', 'on');

步骤 2：先看清有哪些坏数据

sql
SELECT id, name, age, salary, joined, active,
       age ~ '^\s*\d+\s*$'                    AS age_ok,
       salary ~ '^\s*(\d+\.?\d*|\.\d+)\s*$'   AS salary_ok,
       joined ~ '^\d{4}-\d{2}-\d{2}$'         AS joined_ok
FROM raw_import
ORDER BY id::integer;
text
 id | name |  age  |    salary     |   joined   | active | age_ok | salary_ok | joined_ok
----+------+-------+---------------+------------+--------+--------+-----------+-----------
 1  | 张三 | 28    | 15000.50      | 2026-01-15 | true   | t      | t         | t
 2  | 李四 | 35    | 22000         | 2026-03-02 | t      | t      | t         | t
 3  | 王五 |       | 18000.75      | 2026-05-20 | 1      | f      | t         | t
 4  | 赵六 |   42  |               | 2026/07/08 | yes    | t      | f         | f
 5  | 孙七 | abc   | not-a-num     | 2026-08-30 | false  | f      | f         | t
 6  | 周八 | 150   | 99999999999.99| 2026-09-01 | on     | t      | t         | t
(6 rows)

步骤 3：建立目标表并做安全转换

sql
CREATE TABLE clean_users (
  id      integer PRIMARY KEY,
  name    text          NOT NULL,
  age     integer,
  salary  numeric(12, 2),
  joined  date,
  active  boolean       NOT NULL DEFAULT true
);

INSERT INTO clean_users (id, name, age, salary, joined, active)
SELECT id::integer,
       name,
       -- 空串、非法格式一律转 NULL，超范围（>120）也置 NULL
       CASE WHEN age ~ '^\s*\d+\s*$'
                 AND age::integer BETWEEN 1 AND 120
            THEN age::integer
            ELSE NULL END,
       -- 先清洗空串再转 numeric，超精度由 numeric(12,2) 兜底
       CASE WHEN salary ~ '^\s*(\d+\.?\d*|\.\d+)\s*$'
            THEN NULLIF(trim(salary), '')::numeric(12, 2)
            ELSE NULL END,
       -- 统一分隔符后转 date
       CASE WHEN joined ~ '^\d{4}[-/]\d{2}[-/]\d{2}$'
            THEN replace(joined, '/', '-')::date
            ELSE NULL END,
       -- 覆盖常见布尔写法
       CASE lower(trim(active))
         WHEN 'true' THEN true  WHEN 't' THEN true
         WHEN 'yes'  THEN true  WHEN '1' THEN true  WHEN 'on' THEN true
         WHEN 'false' THEN false WHEN 'f' THEN false
         WHEN 'no'  THEN false WHEN '0' THEN false WHEN 'off' THEN false
         ELSE true
       END
FROM raw_import
-- 主键不能为空，单独过滤
WHERE id ~ '^\d+$';
text
INSERT 0 6

步骤 4：核对结果

sql
SELECT id, name, age, salary, joined, active FROM clean_users ORDER BY id;
text
 id | name | age |   salary   |   joined   | active
----+------+-----+------------+------------+--------
  1 | 张三 |  28 |   15000.50 | 2026-01-15 | t
  2 | 李四 |  35 |   22000.00 | 2026-03-02 | t
  3 | 王五 |     |   18000.75 | 2026-05-20 | t
  4 | 赵六 |  42 |            | 2026-07-08 | t
  5 | 孙七 |     |            | 2026-08-30 | f
  6 | 周八 | 120 | 99999999999.99 | 2026-09-01 | t
(6 rows)

注意 id=6：age = 150 被 BETWEEN 1 AND 120 挡成 NULL，但 salary 竟然写进去了 99999999999.99——超过 numeric(12,2) 的 10 位整数上限，PostgreSQL 会直接报错而不是截断：

text
ERROR:  numeric field overflow
DETAIL:  A field with precision 12, scale 2 must round to an absolute value less than 10^10.

步骤 5：修正超精度问题，给 salary 加范围判断

sql
UPDATE clean_users SET salary = NULL
WHERE salary >= 10000000000;

SELECT id, name, salary FROM clean_users ORDER BY id;
text
 id | name |   salary
----+------+------------
  1 | 张三 |   15000.50
  2 | 李四 |   22000.00
  3 | 王五 |   18000.75
  4 | 赵六 |
  5 | 孙七 |
  6 | 周八 |
(6 rows)

步骤 6：用 pg_cast 验证几个关键转换的成本

sql
SELECT source, target, context,
       CASE context WHEN 'i' THEN '隐式' WHEN 'a' THEN '赋值' ELSE '显式' END AS ctx_cn
FROM (
  SELECT castsource::regtype::text AS source,
         casttarget::regtype::text AS target,
         castcontext               AS context
  FROM pg_cast
  WHERE (castsource, casttarget) IN (
    ('text'::regtype, 'integer'::regtype),
    ('text'::regtype, 'numeric'::regtype),
    ('text'::regtype, 'date'::regtype),
    ('text'::regtype, 'boolean'::regtype)
  )
) s
ORDER BY target;
text
 source | target  | context | ctx_cn
--------+---------+---------+--------
 text   | boolean | e       | 显式
 text   | date    | e       | 显式
 text   | integer | e       | 显式
 text   | numeric | e       | 显式
(4 rows)

全是 e（显式）——这意味着查询里不会自动帮你转，必须显式写，这正是上面每个 CASE 分支里都要 :: 的原因。

案例二：让自定义类型融入内置运算（CREATE CAST 实战） ​

目标：定义一个 positive_int 类型（本质是带约束的 integer），并让它能与内置整数自由比较。

步骤 1：定义类型与 I/O 函数

sql
DROP TABLE IF EXISTS metrics CASCADE;
DROP TYPE IF EXISTS positive_int CASCADE;
DROP FUNCTION IF EXISTS positive_int_in(cstring);
DROP FUNCTION IF EXISTS positive_int_out(positive_int);

CREATE FUNCTION positive_int_in(cstring) RETURNS integer
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT CASE
           WHEN $1 ~ '^\d+$' AND $1::integer > 0 THEN $1::integer
           ELSE NULL
         END;
$$;

CREATE FUNCTION positive_int_out(integer) RETURNS cstring
LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT $1::text;
$$;

步骤 2：定义基于壳的类型（Shell Type）

sql
CREATE TYPE positive_int;   -- 先建壳

CREATE FUNCTION positive_int_in(cstring) RETURNS positive_int
LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $1::integer; $$;

CREATE FUNCTION positive_int_out(positive_int) RETURNS cstring
LANGUAGE sql IMMUTABLE STRICT AS $$ SELECT $1::integer::text; $$;

CREATE TYPE positive_int (
  INPUT          = positive_int_in,
  OUTPUT         = positive_int_out,
  INTERNALLENGTH = 4,
  PASSEDBYVALUE,
  ALIGNMENT      = int4,
  STORAGE        = plain
);

步骤 3：注册到 integer 的隐式转换

sql
-- positive_int → integer：无损，可以隐式
CREATE CAST (positive_int AS integer) WITHOUT FUNCTION AS IMPLICIT;

这里能用 WITHOUT FUNCTION 是因为两者二进制表示完全相同（都是 4 字节整数）。

步骤 4：验证能直接参与内置运算

sql
CREATE TABLE metrics (
  id    serial PRIMARY KEY,
  value positive_int NOT NULL
);

INSERT INTO metrics (value) VALUES ('10'), ('20'), ('30');

-- 无需显式转换即可比较、求和
SELECT id, value, value + 5 AS plus5
FROM metrics
WHERE value > 15
ORDER BY value;
text
 id | value | plus5
----+-------+-------
  2 |    20 |    25
  3 |    30 |    35
(2 rows)
sql
-- 也能直接 JOIN 内置整数表
SELECT m.id, m.value
FROM metrics m
JOIN (VALUES (10), (30)) AS v(n) ON m.value = v.n
ORDER BY m.id;
text
 id | value
----+-------
  1 |    10
  3 |    30
(2 rows)

步骤 5：查看注册结果

sql
SELECT castsource::regtype AS source,
       casttarget::regtype AS target,
       castfunc            AS func,
       castcontext         AS ctx,
       castmethod          AS method
FROM pg_cast
WHERE castsource = 'positive_int'::regtype;
text
    source    | target  | func | ctx | method
--------------+---------+------+-----+--------
 positive_int | integer |    0 | i   | b
(1 row)

func = 0 加 method = b（binary）说明走的是二进制强转，没有任何函数调用开销——这就是"零成本抽象"。

步骤 6：清理

sql
DROP TABLE metrics;
DROP TYPE positive_int CASCADE;
text
NOTICE:  drop cascades to cast from positive_int to integer

案例三：排查一个真实的类型不匹配故障 ​

目标：模拟线上常见故障——WHERE 里的类型不匹配导致索引失效 + 报错。

步骤 1：准备数据

sql
DROP TABLE IF EXISTS events CASCADE;

CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  code        varchar(20) NOT NULL,
  payload     text,
  occurred_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO events (code, payload)
SELECT 'EVT' || lpad(i::text, 5, '0'), '{"seq":' || i || '}'
FROM generate_series(1, 5000) AS i;

CREATE INDEX idx_events_code ON events (code);
CREATE INDEX idx_events_occurred ON events (occurred_at);
ANALYZE events;

步骤 2：第一个故障——字符串与 varchar 比较没问题，但数字列被转成 text

sql
-- 慢：occurred_at 被转成 text 后比较
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events
WHERE occurred_at::text LIKE '2026-09-22%';
text
                          QUERY PLAN
---------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on events (actual rows=5000 loops=1)
         Filter: ((occurred_at)::text ~~ '2026-09-22%'::text)
(3 rows)

步骤 3：改写成范围条件

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) FROM events
WHERE occurred_at >= '2026-09-22'::date
  AND occurred_at <  '2026-09-23'::date;
text
                             QUERY PLAN
-------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Index Only Scan using idx_events_occurred on events (actual rows=5000 loops=1)
         Index Cond: ((occurred_at >= '2026-09-22'::date) AND (occurred_at < '2026-09-23'::date))
         Heap Fetches: 0
(4 rows)

从 Seq Scan 变成 Index Only Scan，且 Heap Fetches: 0 说明索引完全覆盖。

步骤 4：第二个故障——varchar 与 text 混用造成参数推断失败

sql
-- 看似正常
SELECT count(*) FROM events WHERE code = 'EVT00001';
text
 count
-------
     1
(1 row)

换个写法就出问题：把 code 和 text 拼接后再比较，会触发 varchar 与 text 的结果类型推断：

sql
EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE code || '' = 'EVT00001';
text
                    QUERY PLAN
--------------------------------------------------
 Aggregate
   ->  Seq Scan on events
         Filter: (((code)::text = 'EVT00001'::text))
(3 rows)

索引失效了——因为表达式是 code::text || ''，不是裸 code。这是拼接引入隐式转换的典型后果。结论：不要在索引列上加任何运算或转换，哪怕看起来"没变"。

步骤 5：用表达式索引救回来

如果业务上确实需要这种表达式，可以建匹配的表达式索引：

sql
CREATE INDEX idx_events_code_text ON events ((code::text));

EXPLAIN (COSTS OFF)
SELECT count(*) FROM events WHERE code::text = 'EVT00001';
text
                             QUERY PLAN
-------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using idx_events_code_text on events
         Index Cond: ((code)::text = 'EVT00001'::text)
(3 rows)

步骤 6：全局体检——找出库里被"隐式转换"绑架的索引

sql
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND indexdef ~ '::'
  AND indexdef !~ '\('
ORDER BY tablename, indexname;
text
 schemaname | tablename |      indexname       |                       indexdef
------------+-----------+----------------------+-------------------------------------------------------
 public     | events    | idx_events_code_text | CREATE INDEX idx_events_code_text ON public.events USING btree (((code)::text))
 public     | events    | idx_events_occurred  | CREATE INDEX idx_events_occurred ON public.events USING btree (occurred_at)
(2 rows)

注意事项 ​

  1. :: 在不同数据库语义不同。 PostgreSQL 里 :: 是类型转换，但某些数据库（如 SQL Server 的 ::）或语言（C++ 作用域解析）含义完全不同，跨库迁移时注意替换成 CAST。

  2. 字符串转数字不会"尽力而为"。 '3.14'::integer、'abc'::integer、''::integer 全部报错，没有自动截断或返回 0 的兜底行为。

  3. text → 任意类型 依赖输出格式。 如果升级 PostgreSQL 大版本，某个类型的输出格式变了（例如某些 float 的精度显示），原来能转的字符串可能突然转不了。生产库升级前要做回归。

  4. timestamptz → date 依赖会话时区。 同一个 timestamptz 在 UTC 和 Asia/Shanghai 会话下转出的 date 可能差一天。跨时区系统必须显式 AT TIME ZONE 指定。

  5. ::date 写在索引列上会失效。 这是最高频的性能事故。要么改写成范围条件，要么建表达式索引。

  6. 隐式转换可能让函数解析"歧义"。 加 AS IMPLICIT 之前全量跑回归测试；有了隐式转换后报 operator is not unique 的锅往往是它。

  7. WITHOUT FUNCTION 前提是二进制兼容。 不兼容时 PostgreSQL 会拒绝创建，不要为了"性能好"硬用。它跳过一切校验，用错了会产生莫名其妙的数据损坏。

  8. numeric 的精度转换会报错而非静默截断。 超范围写 numeric(12,2) 列会得到 numeric field overflow，不是截断。ETL 时务必先判断范围。

  9. 浮点转整数会截断（round 而非 truncate 取决于写法）。 '3.9'::numeric::integer 得到 4（四舍五入），trunc('3.9'::numeric)::integer 得到 3。需要哪种语义要显式写清楚。

  10. CAST 不能改变值的"语义"。 '2026-09-22'::text::integer 不会变成日期数字，它只是先得到字符串再做（失败的）解析。别把"格式转换"和"语义转换"混为一谈。

  11. 数组与复合类型的转换更严格。 '{1,2}'::text[]::integer[] 可以，但 '{"a":1}'::text[] 会失败（元素解析不了）。数组转换要求每个元素都能转。

  12. unknown 字面量在 UNION / CASE 里可能被推断为 text。 SELECT 'a' UNION SELECT 1 会报类型不匹配，因为第一支推断不出明确类型。显式写 'a'::text 或 1::text。

  13. pg_cast 里查不到的转换不代表不能转。 所有类型到 text（以及 text 到所有类型）的转换是内置保证的，不进 pg_cast。

  14. 转换函数可能不是 IMMUTABLE。 例如 timestamptz::date 依赖会话时区，它是 STABLE 而非 IMMUTABLE——这也是为什么它不能直接用于表达式索引（必须先 AT TIME ZONE 固定下来）。

  15. 批量转换注意锁与重写。 ALTER TABLE ... ALTER COLUMN ... TYPE new_type USING ... 会重写整表并持有 ACCESS EXCLUSIVE 锁，大表要安排维护窗口或用"加新列 + 回填 + 切换"的方案。

最佳实践 ​

  1. 日常一律用 ::，跨库 SQL 用 CAST(... AS ...)。 简单、清晰、无歧义。

  2. 不要依赖隐式转换，能显式就显式。 显式的转换是给人看的文档，也是给优化器的提示。WHERE amount > 100::numeric 比 WHERE amount > 100 更明确。

  3. 索引列上永远不做转换。 改成范围条件（>= / <）或建表达式索引。这是 DBA 的基本功。

  4. 写日期条件用"半开区间"。 >= '2026-09-22' 且 < '2026-09-23'，既用得上索引，又不会因为微秒精度漏数据。

  5. 转换前先校验，别指望数据库兜底。 在应用层或 ETL 层用正则/类型检查过滤非法输入，数据库的报错是最后的防线，不是第一道。

  6. 给转换写统一的安全函数。 例如 try_int(text) RETURNS integer（内部用 EXCEPTION 或正则判断），把"安全转换"的能力补齐。PostgreSQL 不提供 TRY_CAST，自己封装一个。

  7. 自定义类型默认用显式转换。 只有确认"无损且语义天然"时才升到 AS IMPLICIT，且必须做全量回归。

  8. 转换要写在同一层，不要跨层乱转。 数据入库时就转成最终类型，别在查询里频繁 ::——既影响性能（无法用索引），也说明表设计有问题。

  9. 大版本升级前做转换回归测试。 重点测 float/numeric 的文本表示、json/jsonb 的格式化输出、时区相关的日期转换。

  10. 给关键字段选择"天然匹配"的类型。 code 用 text 而不是 varchar(20)（避免 varchar 与 text 混用时的转换）、时间用 timestamptz 而不是 text。类型选对了，转换问题就少一大半。

  11. 表达式索引的表达式必须"逐字一致"。 建索引和写查询时把表达式复制粘贴，别手写，避免差一个空格或括号导致索引失配。

  12. 用 EXPLAIN 而不是"猜测"来验证索引是否生效。 看到 Index Cond 而不是 Filter 才算真正走上索引。

下一节

继续学习：tsvector 与 tsquery

参考链接 ​