用 CAST() 和 :: 做数据类型转换 —— 示例和注意点

想象一下，你想把两个数字加起来，但其中一个参数其实是字符串。或者你想按年份筛选数据，但你的日期字段是完整的带时间格式。PostgreSQL 可能会对你的想法表示不满，这时候你就得手动把数据转成合适的类型。

类型转换能帮你：

• 让数据更容易处理，可以直接参与计算。
• 生成更易读、更结构化的数据，方便导出或分析。
• 解决数据类型不匹配导致的各种报错。

数据类型转换基础：CAST() 和 ::

1. CAST() —— 万能的“官方”转换方法

CAST() 函数就是把一个值从一种数据类型转成另一种的“官方”方式。它就像个通用翻译器。

语法：

CAST(值 AS 目标_数据类型)

例子 1：把字符串转成数字。

SELECT CAST('123' AS INTEGER); -- 结果: 123

例子 2：把数字转成字符串。

SELECT CAST(123 AS TEXT); -- 结果: '123'

例子 3：把日期转成字符串。

SELECT CAST(NOW() AS TEXT); -- 结果: '2023-10-25 15:00:00.000000'

2. 操作符 :: —— 简洁的替代方案

如果你觉得 CAST() 太啰嗦，PostgreSQL 还提供了 :: 语法。这是个很流行的简写方式，写起来超快。

语法：

值::目标_数据类型

例子 1：把字符串转成数字。

SELECT '123'::INTEGER; -- 结果: 123

例子 2：把数字转成字符串。

SELECT 123::TEXT; -- 结果: '123'

例子 3：把日期转成字符串。

SELECT NOW()::TEXT; -- 结果: '2023-10-25 15:00:00.000000'

类型转换的实际用法举例

1. 数字和字符串的转换

比如你有个 students 表，student_id 字段存的是字符串，但你想和数字做比较。不转换类型是没戏的。

所以你得在查询里把 student_id 明确转成数字：

SELECT *
FROM students
WHERE student_id::INTEGER = 101;

用 CAST() 的写法也一样：

SELECT *
FROM students
WHERE CAST(student_id AS INTEGER) = 101;

日期的转换

有时候你想提取日期的一部分，或者把它转成字符串，类型转换也很有用。比如你有个 courses 表，start_date 字段存的是课程开始日期。

例子：把日期转成数字类型的年份。

SELECT start_date::DATE, start_date::TEXT, start_date::TIMESTAMP
FROM courses;

结果会是这样：

例子：把日期转成“纯字符串”。

SELECT TO_CHAR(start_date, 'DD-MM-YYYY')

结果会是这样：

报错：什么时候类型转换会翻车？

类型转换不是万能的。有时候 PostgreSQL 会直接告诉你“我不懂你想干嘛”。比如你想把看起来像数字的文本转成数字，但里面混进了奇怪的字符。

错误示例：

SELECT '123abc'::INTEGER;
-- 错误:  invalid input syntax for type integer: "123abc"

为了避免这种问题，最好提前确保数据真的能转成你想要的类型。比如可以用正则表达式或者额外的检查。