类型转换

在 PostgreSQL 中，类型转换是将数据从一种数据类型转换为另一种数据类型的过程。这是数据库操作中非常重要的基础知识，特别是当我们需要在不同数据类型之间进行转换时。

类型转换语法

PostgreSQL 提供了两种等效的类型转换语法：

CAST语法

CAST (expression AS type)

双冒号语法

expression::type

• CAST 语法是符合 SQL 标准的写法
• 双冒号(::)语法是 PostgreSQL 特有的历史用法

虽然两种语法是等效的，但在编写可移植的 SQL 代码时，推荐使用标准的 `CAST` 语法。

类型转换工作原理

当我们对一个已知类型的表达式值应用类型转换时，PostgreSQL 会执行运行时类型转换。这种转换只有在系统中定义了合适的类型转换操作时才能成功。

示例：基本类型转换

CAST语法示例

-- 将整数转换为文本
SELECT CAST(123 AS text);

-- 将文本转换为日期
SELECT CAST('2023-10-15' AS date);

-- 将浮点数转换为整数(会截断小数部分)
SELECT CAST(45.95 AS integer);

双冒号语法示例

-- 将整数转换为文本
SELECT 123::text;

-- 将文本转换为日期
SELECT '2023-10-15'::date;

-- 将浮点数转换为整数(会截断小数部分)
SELECT 45.95::integer;

输出结果

 text
------
 123

    date
------------
 2023-10-15

 integer
---------
       45

自动类型转换与显式类型转换

自动类型转换

在某些情况下，PostgreSQL 会自动应用类型转换，尤其是当表达式的目标类型没有歧义时（例如，将值赋给表列时）。

CREATE TABLE employee (
  id serial PRIMARY KEY,
  birth_date date
);

-- PostgreSQL 会自动将字符串转换为日期类型
INSERT INTO employee (birth_date) VALUES ('1990-01-15');

自动类型转换仅适用于在系统目录中标记为"可以隐式应用"的转换。其他转换必须使用显式转换语法。这是为了防止意外的转换被默默地应用。

显式类型转换

在以下情况下，应该使用显式类型转换：

1. 当自动转换不适用时
2. 为了提高代码的可读性和明确性
3. 当需要确保按照特定方式进行转换时

-- 显式转换确保按预期处理数据类型
SELECT CAST('100' AS integer) + 50;  -- 结果: 150

类似函数的转换语法

PostgreSQL 还支持一种类似函数调用的类型转换语法：

typename(expression)

例如：

SELECT integer('100');  -- 将字符串 '100' 转换为整数

但是，这种语法有一些限制：

• 仅适用于名称也作为函数名称有效的类型
• 不能用于 double precision (但可以用于等效的 float8)
• 对于 interval、time 和 timestamp 类型，必须使用双引号引起来才能使用

由于语法不一致性和潜在混淆，建议避免使用类似函数的转换语法，而优先选择标准的 `CAST` 或 `::` 语法。

类型转换的内部机制

当使用类型转换语法进行运行时转换时，PostgreSQL 实际上是调用了已注册的转换函数。按照惯例，这些转换函数通常与其输出类型具有相同的名称。

常见类型转换示例

源类型	目标类型	示例	结果	说明
integer	text	CAST(123 AS text)	'123'	整数转为文本
text	integer	CAST('123' AS integer)	123	文本转为整数
numeric	integer	CAST(45.95 AS integer)	45	小数转整数(截断)
text	date	CAST('2023-10-15' AS date)	2023-10-15	文本转日期
text	boolean	CAST('true' AS boolean)	true	文本转布尔值
timestamp	date	CAST('2023-10-15 14:30:00' AS date)	2023-10-15	时间戳转日期

实际应用场景

场景 1：数据导入和清洗

当从外部源导入数据时，经常需要进行类型转换：

-- 从CSV导入的数据通常是文本，需要转换为适当的类型
UPDATE imported_data
SET
    price = CAST(price_text AS numeric),
    purchase_date = CAST(date_text AS date),
    is_active = CAST(status_text AS boolean);

场景 2：条件过滤

-- 查找特定年份的订单
SELECT * FROM orders
WHERE CAST(order_date AS text) LIKE '2023%';

-- 更高效的替代方案
SELECT * FROM orders
WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

场景 3：数据聚合和分析

-- 按月份分组分析销售数据
SELECT
    CAST(DATE_TRUNC('month', order_date) AS date) AS month,
    SUM(amount) AS monthly_total
FROM sales
GROUP BY month
ORDER BY month;

常见错误和注意事项

类型转换可能导致以下问题：

1. 数据截断：将较大范围的类型转换为较小范围的类型可能导致数据丢失

   SELECT CAST(1234.56 AS smallint);  -- 可能导致溢出错误

2. 无效的转换：尝试转换不兼容的类型会导致错误

   SELECT CAST('hello' AS integer);  -- 错误：无效的整数语法

3. 精度丢失：浮点数转换可能导致精度问题

   SELECT CAST(CAST(1.23456789 AS float) AS numeric);  -- 可能不会返回原始的精确值

小结

• PostgreSQL 提供了多种类型转换语法，其中 CAST(expression AS type) 和 expression::type 是最常用的
• 自动类型转换在某些情况下会发生，但显式转换通常更清晰和可预测
• 了解类型转换的工作原理和常见陷阱可以帮助避免数据处理错误
• 在实际应用中，类型转换是数据清洗和转换过程中不可或缺的工具

TIP

熟练掌握类型转换对于编写高效和正确的 PostgreSQL 查询至关重要。始终考虑转换可能带来的数据变化，并在必要时进行数据有效性检查。