数据类型的比较和转换

为啥我们要搞这些数据类型的比较和转换？想象一下，你表里有个字符串 "42"，你想拿它和数字 42 比较。乍一看，好像没啥区别。但对数据库来说，这俩完全不是一回事。PostgreSQL 可不会自动猜你想干嘛。如果你不清楚它是怎么比较不同类型的值，结果可能很奇怪，甚至直接报错。

转换也是一样。有时候你得把文本变成数字，才能统计某些东西的数量。有时候反过来——数字要美美地显示成字符串。或者你想把日期格式化成 "01.01.2025"，因为用户看着更顺眼。

再比如，你有个精确值存在 NUMERIC 里，但做科学计算时得用 FLOAT。这种情况就必须手动转换一下。

好消息是，PostgreSQL 这方面做得很溜。它给你灵活又好懂的工具。关键是要知道怎么用，别怕多看看底层原理。我们这节课就来搞明白这些。

数据类型的比较

PostgreSQL 很聪明——比如你拿 INTEGER 和 NUMERIC 比较，它会自动把它们转成兼容的类型，然后比较。没毛病，毕竟都是数字。

但如果你想拿字符串和布尔值（TEXT 和 BOOLEAN）比较，数据库就懵了。这俩类型本质完全不同，PostgreSQL 直接报错。你要是想把 "42" 这个字符串和数字 42 比较，不先手动转换，也会出问题。

实际操作长这样，下面这个例子会报错：

SELECT '42' = 42; -- 报错！字符串和数字不能直接比较

这样写才对：

SELECT '42'::INTEGER = 42; -- TRUE

这里我们明确告诉数据库：“拜托，先把 '42' 转成数字”。这就是 ::类型 语法的用法，之前也提过。PostgreSQL 喜欢你和它说话要清楚。

数字类型的比较

数字类型（INTEGER、NUMERIC、REAL）一般都能互相比较，没啥难度：

SELECT 42 = 42.0; -- TRUE
SELECT 42::REAL = 42.0; -- TRUE
SELECT 42.0::NUMERIC = 42; -- TRUE

不过要小心浮点数（REAL）。因为精度有限，浮点数有时候会很迷。比如：

SELECT 0.1 + 0.2 = 0.3; -- FALSE

这不是编程圈最有名的谜题吗？这里比较结果是 FALSE，因为计算机存储小数有点特殊。

文本类型的比较

处理文本类型时，你可以比较 CHAR、VARCHAR 和 TEXT，PostgreSQL 会自动把它们转成兼容类型：

SELECT 'Hello' = 'Hello'::TEXT; -- TRUE
SELECT 'World'::CHAR(5) = 'World'::VARCHAR; -- TRUE

注意 CHAR(n) 的长度：如果字符串比指定长度短，PostgreSQL 会自动补空格。

数据类型的转换

PostgreSQL 提供了几种数据类型转换的方法。我们主要看两种：

方法1：显式转换（CAST）

CAST 操作符可以指定怎么把一种类型转成另一种。比如：

SELECT CAST('42' AS INTEGER); -- 把字符串 '42' 转成数字 42

这个方法让SQL代码更易读。

方法2：简写（::）

PostgreSQL 还支持一种更短的写法——用 ::。其实和上面一样，只是更简洁：

SELECT '42'::INTEGER; -- 跟 CAST('42' AS INTEGER) 一样

自动转换

很多时候 PostgreSQL 会自动转换类型。比如在字符串字段里用数字：

SELECT '42' = 42::TEXT; -- TRUE

不过，别太依赖自动转换，因为别人看你代码可能会懵。尤其是日期和字符串，最好手动转换。

不同数据类型转换的例子

数字转文本

有时候你得把数字转成文本（比如拼消息）：

SELECT 42::TEXT; -- 把数字 42 转成字符串 '42'
SELECT 3.14::TEXT; -- 把数字 3.14 转成字符串 '3.14'

文本转数字

如果字符串里是合法数字，你可以转成数字类型：

SELECT '123'::INTEGER; -- 把字符串 '123' 转成数字 123
SELECT '3.14'::FLOAT;  -- 把字符串 '3.14' 转成数字 3.14

那要是文本不能转呢？比如：

SELECT 'Hello'::INTEGER; -- 报错：'Hello' 不能转成数字

为了避免这种报错，你可以用 TRY_CAST()（PostgreSQL 14 起支持），或者先检查下数据。

日期和文本的互转

转换日期时可以用 TO_CHAR() 和 TO_DATE()：

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'); -- 把日期转成字符串
SELECT TO_DATE('2023-10-25', 'YYYY-MM-DD'); -- 把字符串转成日期

BOOLEAN 和文本的互转

布尔类型 BOOLEAN 也能转成字符串：

SELECT TRUE::TEXT; -- 'true'
SELECT FALSE::TEXT; -- 'false'

反过来也行：

SELECT 'true'::BOOLEAN; -- TRUE
SELECT 'false'::BOOLEAN; -- FALSE

注意，像 'yes' 或 'no' 这种字符串不会自动转成布尔值。

实战：全都用实际例子说话

我们来建个表，演示下不同数据类型的用法：

现在来做些转换操作：

-- 文本转数字
SELECT number_as_text::INTEGER FROM data_types_demo WHERE number_as_text = '42';

-- 日期转文本
SELECT TO_CHAR(actual_date, 'DD/MM/YYYY') FROM data_types_demo;

-- 字符串转日期
SELECT TO_DATE(date_as_text, 'YYYY-MM-DD') FROM data_types_demo;

数据转换时的常见错误

常见错误有：

1. 试图转换格式不对的数据（比如把 'Hello' 转成 INTEGER）。
2. 处理浮点数时的四舍五入和精度问题。
3. 日期转换时格式用错。

为了避免这些坑，建议：

• 转换前一定要检查数据。
• 用错误处理函数（TRY_CAST 或 CASE）。
• 日期转换时一定要明确指定格式。

-- 转换前先检查数据
SELECT 
    CASE 
        WHEN number_as_text ~ '^\d+$' THEN number_as_text::INTEGER
        ELSE NULL
    END AS safe_integer
FROM data_types_demo;

用这种方式，能让你的SQL更安全，防止各种意外！

现在你已经掌握了在PostgreSQL里比较和转换数据类型的基本技能。接下来就是多练、多写、多实践。下节课见！