PostgreSQL CAST 转换数据类型

本文介绍PostgreSQL CAST操作转换数据类型。

1. PostgreSQL CAST介绍

很多时候需要转换数据类型。PostgreSQL 提供了CAST操作实现类型转换。语法如下：

CAST ( expression AS target_type ); 

• 表达式可以是常量、列、表达式
• 目标数据类型即想转换的数据类型

*cast :: operator

除了上面的语法，还有另外的语法实现同样功能；expression::type，举例：

SELECT 
  '100'::INTEGER, 
  '01-OCT-2015'::DATE; 

注意使用cast操作(::)是PostgreSQLT特有功能，非SQL的通用标准。下面通过一些示例展示如何实现类型转换。

2. 示例

2.1 string 转 integer

SELECT CAST ('100' AS INTEGER); 

如果表达式不能转成目标类型，会抛出异常，请看示例：

SELECT 
	CAST ('10C' AS INTEGER); 
[Err] ERROR:  invalid input syntax for integer: "10C" 
LINE 2:  CAST ('10C' AS INTEGER); 

2.2 string 转 date

SELECT 
   CAST ('2015-01-01' AS DATE), 
   CAST ('01-OCT-2015' AS DATE); 

首先转换 2015-01-01 字符串至 2015-01-01； 然后转换 01-OCT-2015 至 2015-10-01.

2.3 string 转 double

SELECT CAST ('10.2' AS DOUBLE); 

哎，怎么报错了：

[Err] ERROR:  type "double" does not exist 
LINE 2:  CAST ('10.2' AS DOUBLE) 

我们使用DOUBLE PRECISION 代替DOUBLE：

SELECT 
   CAST ('10.2' AS DOUBLE PRECISION); 

2.4 string 转 boolean

ELECT  
   CAST('true' AS BOOLEAN), 
   CAST('false' as BOOLEAN), 
   CAST('T' as BOOLEAN), 
   CAST('F' as BOOLEAN); 

输出如下：

true	false	true	false 

2.5 string 转 timestamp

SELECT '2019-06-15 14:30:20'::timestamp; 

输出：

2019-06-15 14:30:20 

2.6 string 转 interval

SELECT '15 minute'::interval, 
 '2 hour'::interval, 
 '1 day'::interval, 
 '2 week'::interval, 
 '3 month'::interval; 

输出：

"00:15:00"	"02:00:00"	"1 day"	"14 days"	"3 mons" 

2.7 使用cast转表字段数据

首先传教ratings表，包括两个列：id, rating；rating的数据类型是varchar(1):

CREATE TABLE ratings ( 
	ID serial PRIMARY KEY, 
	rating VARCHAR (1) NOT NULL 
); 

下面插入一些示例数据：

INSERT INTO ratings (rating) 
VALUES 
	('A'), 
	('B'), 
	('C'); 

因为需求变化，需要使用ratings存储数值，如：1，2，3代替A,B,C:

INSERT INTO ratings (rating) 
VALUES 
	(1), 
	(2), 
	(3); 

查询数据：

select * from  ratings ; 

输出:

1	"A" 
2	"B" 
3	"C" 
4	"1" 
5	"2" 
6	"3" 

现在我们需要转换rating列所有值为数值，A,B,C转为0，下面使用case表达式结合cast操作实现查询：

SELECT 
	id, 
	CASE 
		WHEN rating~E'^\\d+$' THEN 
			CAST (rating AS INTEGER) 
		ELSE 
			0 
		END as rating 
FROM 
	ratings; 

输出：

1	0 
2	0 
3	0 
4	1 
5	2 
6	3 

case检查rating，如果匹配整数模式，则转换为整数，否则返回0.

3. 总结

本文学习了如何使用PostgreSQL CAST转换数据类型，并通过几个实例展示多种数据类型之间转换。

本文参考链接：https://blog.csdn.net/neweastsun/article/details/111414641