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转换数据类型，并通过几个实例展示多种数据类型之间转换。