PostgreSQL教程FG047-PG类型转换：隐式转换与显式转换（CAST）规范

本文档详细介绍PostgreSQL类型转换的使用方法和规范，包括隐式转换和显式转换（CAST），风哥教程参考PostgreSQL官方文档内容，适合数据库管理员和开发人员在生产环境中使用类型转换进行数据处理。

Part01-基础概念与理论知识

1.1 PostgreSQL类型转换概述

PostgreSQL支持两种类型转换方式：隐式转换和显式转换。隐式转换是由PostgreSQL自动执行的，而显式转换需要用户明确指定。类型转换是数据库操作中的常见需求，正确使用类型转换可以避免数据类型不匹配的错误，提高查询的灵活性。更多视频教程www.fgedu.net.cn

类型转换的必要性：

数据类型不匹配：当操作数的数据类型不同时，需要进行类型转换
函数参数要求：某些函数要求特定类型的参数
查询条件：在WHERE子句中比较不同类型的数据
数据显示：将数据转换为特定格式进行显示

1.2 隐式类型转换

隐式类型转换是由PostgreSQL自动执行的类型转换，不需要用户明确指定。PostgreSQL会根据操作的上下文自动选择合适的类型转换路径。

1.3 显式类型转换

显式类型转换需要用户明确指定，使用CAST函数或::操作符。显式类型转换可以确保转换的正确性和一致性，避免隐式转换可能带来的问题。

风哥提示：显式类型转换可以提高代码的可读性和可维护性，建议在关键操作中使用显式转换。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 类型转换最佳实践

— 类型转换最佳实践

— 1. 优先使用显式转换
— 显式转换提高代码可读性和可维护性
SELECT CAST(‘123’ AS INTEGER);
SELECT ‘123’::INTEGER;

— 2. 避免不必要的类型转换
— 不必要的类型转换会影响性能
— 好的做法：直接使用正确类型的数据
INSERT INTO fgedu_users (age) VALUES (25);
— 不好的做法：进行不必要的转换
INSERT INTO fgedu_users (age) VALUES (CAST(’25’ AS INTEGER));

— 3. 注意转换的安全性
— 确保转换不会导致数据丢失或错误
SELECT CAST(‘123.45’ AS INTEGER); — 会截断小数部分
SELECT CAST(‘abc’ AS INTEGER); — 会报错

— 4. 考虑使用TRY_CAST
— TRY_CAST在转换失败时返回NULL，而不是报错
SELECT TRY_CAST(‘123’ AS INTEGER); — 返回123
SELECT TRY_CAST(‘abc’ AS INTEGER); — 返回NULL

2.2 性能考虑

类型转换的性能考虑：

避免在WHERE子句中进行类型转换：会导致索引失效
避免在JOIN条件中进行类型转换：会影响连接性能
使用合适的转换路径：选择效率最高的转换路径
考虑使用生成列：对于频繁需要转换的列，使用生成列存储转换结果

风哥教程针对生产环境建议：在性能敏感的查询中，避免不必要的类型转换。from PostgreSQL视频:www.itpux.com

2.3 错误处理

类型转换的错误处理：

使用TRY_CAST：在转换可能失败的情况下使用TRY_CAST
添加转换检查：在转换前检查数据的有效性
使用异常处理：在PL/pgSQL中使用异常处理捕获转换错误
提供默认值：在转换失败时提供合理的默认值

Part03-生产环境项目实施方案

3.1 CAST语法详解

3.1.1 CAST函数语法

— CAST函数语法

— 基本语法
CAST(expression AS type)

— 示例
SELECT CAST(‘123’ AS INTEGER); — 将字符串转换为整数
SELECT CAST(123 AS VARCHAR); — 将整数转换为字符串
SELECT CAST(‘2026-04-07’ AS DATE); — 将字符串转换为日期
SELECT CAST(NOW() AS DATE); — 将时间戳转换为日期

3.1.2 ::操作符语法

— ::操作符语法

— 基本语法
expression::type

— 示例
SELECT ‘123’::INTEGER; — 将字符串转换为整数
SELECT 123::VARCHAR; — 将整数转换为字符串
SELECT ‘2026-04-07’::DATE; — 将字符串转换为日期
SELECT NOW()::DATE; — 将时间戳转换为日期

3.2 类型转换函数

3.2.1 常用类型转换函数

— 常用类型转换函数

— 字符串转换函数
SELECT TO_CHAR(123, ‘FM999’); — 将整数转换为格式化的字符串
SELECT TO_CHAR(NOW(), ‘YYYY-MM-DD’); — 将时间戳转换为格式化的字符串

— 数值转换函数
SELECT TO_NUMBER(‘123.45’, ‘999.99’); — 将字符串转换为数值
SELECT TO_INT(‘123’); — 将字符串转换为整数（PostgreSQL 18+）

— 日期时间转换函数
SELECT TO_DATE(‘2026-04-07’, ‘YYYY-MM-DD’); — 将字符串转换为日期
SELECT TO_TIMESTAMP(‘2026-04-07 10:30:45’, ‘YYYY-MM-DD HH24:MI:SS’); — 将字符串转换为时间戳

— 布尔值转换函数
SELECT TO_BOOLEAN(‘true’); — 将字符串转换为布尔值
SELECT TO_BOOLEAN(‘1’); — 将字符串转换为布尔值

— 数组转换函数
SELECT ARRAY[‘1’, ‘2’, ‘3’]::INTEGER[]; — 将字符串数组转换为整数数组

3.2.2 类型转换函数执行示例

— 类型转换函数执行示例

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT CAST(‘123’ AS INTEGER), ‘456’::INTEGER;”

— 输出：
— cast | integer
— ——+———
— 123 | 456
— (1 row)

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT TO_CHAR(NOW(), ‘YYYY-MM-DD HH24:MI:SS’), TO_DATE(‘2026-04-07’, ‘YYYY-MM-DD’);”

— 输出：
— to_char | to_date
— ————————–+————
— 2026-04-07 11:30:45 | 2026-04-07
— (1 row)

— 执行
$ psql -h 192.168.1.100 -p 5432 -U fgedu -d fgedudb -c “SELECT TRY_CAST(‘123’ AS INTEGER), TRY_CAST(‘abc’ AS INTEGER);”

— 输出：
— try_cast | try_cast
— ———-+———-
— 123 |
— (1 row)

3.3 常见类型转换

3.3.1 字符串与数值之间的转换

— 字符串与数值之间的转换

— 字符串转整数
SELECT CAST(‘123’ AS INTEGER);
SELECT ‘123’::INTEGER;

— 字符串转浮点数
SELECT CAST(‘123.45’ AS DECIMAL(10,2));
SELECT ‘123.45’::DECIMAL(10,2);

— 数值转字符串
SELECT CAST(123 AS VARCHAR);
SELECT 123::VARCHAR;
SELECT TO_CHAR(123, ‘FM999’);

— 浮点数转字符串
SELECT TO_CHAR(123.45, ‘FM999.99’);

3.3.2 字符串与日期时间之间的转换

— 字符串与日期时间之间的转换

— 字符串转日期
SELECT CAST(‘2026-04-07’ AS DATE);
SELECT ‘2026-04-07’::DATE;
SELECT TO_DATE(‘2026-04-07’, ‘YYYY-MM-DD’);

— 字符串转时间
SELECT CAST(’10:30:45′ AS TIME);
SELECT ’10:30:45′::TIME;

— 字符串转时间戳
SELECT CAST(‘2026-04-07 10:30:45’ AS TIMESTAMP);
SELECT ‘2026-04-07 10:30:45’::TIMESTAMP;
SELECT TO_TIMESTAMP(‘2026-04-07 10:30:45’, ‘YYYY-MM-DD HH24:MI:SS’);

— 日期时间转字符串
SELECT CAST(NOW() AS VARCHAR);
SELECT NOW()::VARCHAR;
SELECT TO_CHAR(NOW(), ‘YYYY-MM-DD HH24:MI:SS’);

3.3.3 数值与日期时间之间的转换

— 数值与日期时间之间的转换

— 数值转时间间隔
SELECT CAST(3600 AS INTERVAL); — 3600秒 = 1小时
SELECT 3600::INTERVAL;

— 时间间隔转数值
SELECT EXTRACT(EPOCH FROM INTERVAL ‘1 hour’); — 3600

— 数值转日期（自2000-01-01起的天数）
SELECT CAST(9635 AS DATE); — 2000-01-01 + 9635天

— 日期转数值（自2000-01-01起的天数）
SELECT EXTRACT(EPOCH FROM (CURRENT_DATE – ‘2000-01-01’::DATE)) / (24 * 3600);

风哥提示：类型转换是数据库操作中的常见需求，掌握不同类型之间的转换方法对于数据处理非常重要。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 隐式类型转换案例

— 隐式类型转换案例

— 1. 数值与字符串比较
— PostgreSQL会自动将字符串转换为数值进行比较
SELECT * FROM fgedu_products WHERE price > ‘1000’;

— 2. 日期与字符串比较
— PostgreSQL会自动将字符串转换为日期进行比较
SELECT * FROM fgedu_orders WHERE order_date > ‘2026-04-01’;

— 3. 函数参数隐式转换
— PostgreSQL会自动转换函数参数类型
SELECT SUBSTRING(12345::INTEGER, 2, 3); — 自动将整数转换为字符串

— 4. 算术运算中的隐式转换
— PostgreSQL会自动转换操作数类型
SELECT 1 + ‘2’; — 结果为3，自动将字符串转换为整数
SELECT 1.5 + 2; — 结果为3.5，自动将整数转换为浮点数

4.2 显式类型转换案例

— 显式类型转换案例

— 1. 数据导入导出
— 将数值转换为字符串格式导出
COPY (SELECT id, TO_CHAR(price, ‘9999.99’) FROM fgedu_products) TO ‘/data/products.csv’ DELIMITER ‘,’ CSV HEADER;

— 从CSV导入数据并转换类型
COPY fgedu_products (id, name, price, stock) FROM ‘/data/products.csv’ DELIMITER ‘,’ CSV HEADER;

— 2. 报表生成
— 生成格式化的报表数据
SELECT
TO_CHAR(order_date, ‘YYYY-MM-DD’) AS order_date,
TO_CHAR(SUM(order_amount), ‘999999.99’) AS total_amount
FROM fgedu_orders
GROUP BY order_date
ORDER BY order_date;

— 3. 数据验证
— 验证输入数据的有效性
SELECT
customer_id,
CASE
WHEN TRY_CAST(phone AS INTEGER) IS NOT NULL THEN ‘Valid’
ELSE ‘Invalid’
END AS phone_validity
FROM fgedu_customers;

— 4. 日期时间处理
— 提取日期时间的特定部分
SELECT
order_id,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
EXTRACT(DAY FROM order_date) AS order_day
FROM fgedu_orders;

4.3 类型转换性能影响案例

— 类型转换性能影响案例

— 1. 索引失效案例
— 创建索引
CREATE INDEX idx_fgedu_orders_order_date ON fgedu_orders(order_date);

— 不好的查询：在WHERE子句中进行类型转换，导致索引失效
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date > ‘2026-04-01’::VARCHAR;

— 好的查询：使用正确的类型，使用索引
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date > ‘2026-04-01’::DATE;

— 2. JOIN条件中的类型转换
— 不好的JOIN：在JOIN条件中进行类型转换
EXPLAIN ANALYZE
SELECT *
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id::VARCHAR = c.customer_id_str;

— 好的JOIN：使用相同类型
EXPLAIN ANALYZE
SELECT *
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.customer_id::INTEGER;

— 3. 生成列优化
— 创建生成列存储转换结果
ALTER TABLE fgedu_orders ADD COLUMN order_date_str VARCHAR GENERATED ALWAYS AS (TO_CHAR(order_date, ‘YYYY-MM-DD’)) STORED;

— 创建生成列的索引
CREATE INDEX idx_fgedu_orders_order_date_str ON fgedu_orders(order_date_str);

— 使用生成列进行查询
EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE order_date_str = ‘2026-04-07’;

Part05-风哥经验总结与分享

5.1 类型转换使用技巧

类型转换使用技巧：

显式转换优先：使用显式转换提高代码可读性和可维护性
避免WHERE子句中的转换：会导致索引失效
使用TRY_CAST：在转换可能失败的情况下使用TRY_CAST
合理使用生成列：对于频繁需要转换的列，使用生成列
选择合适的转换函数：根据具体需求选择合适的转换函数

5.2 类型转换常见问题解决

— 类型转换常见问题解决

— 1. 转换失败
— 问题：类型转换失败导致查询错误
— 解决：
— – 使用TRY_CAST函数
— – 添加转换检查
— – 提供默认值

— 2. 索引失效
— 问题：在WHERE子句中进行类型转换导致索引失效
— 解决：
— – 使用正确的类型进行查询
— – 创建合适的索引
— – 使用生成列

— 3. 数据精度丢失
— 问题：类型转换导致数据精度丢失
— 解决：
— – 选择合适的目标类型
— – 使用足够的精度
— – 避免不必要的转换

— 4. 性能问题
— 问题：类型转换导致性能下降
— 解决：
— – 避免不必要的类型转换
— – 使用生成列
— – 优化查询计划

5.3 类型转换性能优化

— 类型转换性能优化

— 1. 索引优化
— – 为常用的转换结果创建索引
— – 使用生成列存储转换结果
— – 避免在WHERE子句中进行类型转换

— 2. 查询优化
— – 提前进行类型转换，避免在查询中重复转换
— – 使用合适的转换函数
— – 避免在JOIN条件中进行类型转换

— 3. 存储优化
— – 选择合适的数据类型存储数据
— – 避免存储需要频繁转换的数据
— – 使用生成列存储常用的转换结果

— 4. 应用层优化
— – 在应用层进行类型转换，减少数据库负担
— – 使用参数化查询，避免类型转换
— – 缓存转换结果，避免重复转换

风哥提示：类型转换是数据库操作中的重要部分，正确使用类型转换可以提高查询的灵活性和准确性，同时避免性能问题。在生产环境中，应该根据具体需求选择合适的转换方式，并注意避免常见的类型转换陷阱。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html