PostgreSQL 整型int与布尔boolean的自动转换设置(含自定义cast与cast规则介绍)

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

标签

PostgreSQL , cast , 数据类型转换 , 自动类型转换 , 隐式转换 , 显示转换 , 整型 , 布尔 , int , boolean


背景

在使用数据库时,经常会遇到一些因为客户端输入的类型与数据库定义的类型不匹配导致的错误问题。

例如数据库定义的是布尔类型,而输入的是整型:

postgres=# create table cas_test(id int, c1 boolean);  
CREATE TABLE  
  
postgres=# \set VERBOSITY verbose  
postgres=# insert into cas_test values (1, int '1');  
ERROR:  42804: column "c1" is of type boolean but expression is of type integer  
LINE 1: insert into cas_test values (1, int '1');  
                                            ^  
HINT:  You will need to rewrite or cast the expression.  
LOCATION:  transformAssignedExpr, parse_target.c:591  

又或者数据库定义的是时间,用户输入的是字符串:

postgres=# create table tbl123(id int, crt_time timestamp);  
CREATE TABLE  
  
postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');  
ERROR:  column "crt_time" is of type timestamp without time zone but expression is of type text  
LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00');  
                                           ^  
HINT:  You will need to rewrite or cast the expression.  

从错误提示来看,数据库已经很清晰的告诉你为什么了。那么怎么让数据库自动转换呢?

PostgreSQL有一个语法,支持数据类型的转换(赋值、参数、表达式 等位置的自动转换)。

postgres=# \h create cast  
Command:     CREATE CAST  
Description: define a new cast  
Syntax:  
CREATE CAST (source_type AS target_type)  
    WITH FUNCTION function_name [ (argument_type [, ...]) ]  
    [ AS ASSIGNMENT | AS IMPLICIT ]  
  
CREATE CAST (source_type AS target_type)  
    WITHOUT FUNCTION  
    [ AS ASSIGNMENT | AS IMPLICIT ]  
  
CREATE CAST (source_type AS target_type)  
    WITH INOUT  
    [ AS ASSIGNMENT | AS IMPLICIT ]  

数据库内置了很多转换法则:

postgres=# \dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 abstime                     | date                        | date                      | in assignment  
 abstime                     | integer                     | (binary coercible)        | no  
 abstime                     | timestamp without time zone | timestamp                 | yes  
 ........  
 integer                     | boolean                     | bool                      | no  

类型的自动转换实际上也是有一定的规则的,例如 赋值、参数 算是两种规则。具体含义见如下文档:

《PostgreSQL 自定义自动类型转换(CAST)》

我们看到整型转布尔是有内置的转换规则的,那么为什么没有自动转呢?

postgres=# \dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 integer                     | boolean                     | bool                      | no  

和自动转换的规则有关,no表示不会自动转换,只有当我们强制指定转换时,才会触发转换的动作:

postgres=# select cast ((int '1') as boolean);  
 bool   
------  
 t  
(1 row)  

pg_cast里面的context转换为可读的内容(e表示no, a表示assignment, 否则表示implicit)

如果让数据库赋值时自动将字符串转换为时间,自动将整型转换为布尔

1、如果数据库已经内置了转换规则,那么可以通过更新系统表的方式,修改自动转换规则。

例如,将这个INT转BOOLEAN的规则,修改为assignment的规则。

postgres=# update pg_cast set castcontext='a' where castsource ='integer'::regtype and casttarget='boolean'::regtype;  
UPDATE 1  

修改后,我们再查看这个转换规则,就变成这样了

\dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 integer                     | boolean                     | bool                      | in assignment  

现在你可以将int自动写入为BOOLEAN了。

postgres=# create table cas_test(id int, c1 boolean);  
CREATE TABLE  
postgres=# insert into cas_test values (1, int '1');  
INSERT 0 1  

2、如果系统中没有两种类型转换的CAST规则,那么我们需要自定义一个。

例如

  
postgres=# create cast (text as timestamp) with inout as ASSIGNMENT;  
CREATE CAST  
  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 text                        | timestamp without time zone | (binary coercible)        | in assignment  

这样就可以自动将TEXT转换为TIMESTAMP了。

postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');  
INSERT 0 1  
postgres=# select * from tbl123;  
 id |      crt_time         
----+---------------------  
  1 | 2017-01-01 10:00:00  
(1 row)  

删掉这个转换,就会报错。

postgres=# drop cast (text as timestamp);  
DROP CAST  
postgres=# insert into tbl123 values (1, text '2017-01-01 10:00:00');  
ERROR:  column "crt_time" is of type timestamp without time zone but expression is of type text  
LINE 1: insert into tbl123 values (1, text '2017-01-01 10:00:00');  
                                           ^  
HINT:  You will need to rewrite or cast the expression.  

3、如果没有内置的转换函数,我们可能需要自定义转换函数来支持这种转换。

例子

自定义一个函数,用于输入TEXT,返回TIMESTAMPTZ

postgres=# create or replace function cast_text_to_timestamp(text) returns timestamptz as $$  
  select to_timestamp($1, 'yyyy-mm-dd hh24:mi:ss');  
$$ language sql strict ;  
CREATE FUNCTION  

建立规则

postgres=# create cast (text as timestamptz) with function cast_text_to_timestamp as ASSIGNMENT;  
CREATE CAST  
  
postgres=# \dC  
                                             List of casts  
         Source type         |         Target type         |         Function          |   Implicit?     
-----------------------------+-----------------------------+---------------------------+---------------  
 text                        | timestamp with time zone    | cast_text_to_timestamp    | in assignment  

现在,输入TEXT,就可以自定转换为timestamptz了。

postgres=# create table tbl1234(id int, crt_time timestamptz);  
CREATE TABLE  
postgres=# insert into tbl1234 values (1, text '2017-01-01 10:10:10');  
INSERT 0 1  

当然,这些类型实际上内部都有内部的存储格式,大多数时候,如果存储格式通用,就可以直接使用INOUT来转换,不需要写转换函数。

仅仅当两种类型在数据库的内部存储格式不一样的时候,需要显示的写函数来转换。

参考

《PostgreSQL 自定义自动类型转换(CAST)》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
12月前
|
关系型数据库 Go PostgreSQL
golang pgx自定义PostgreSQL类型
golang的pgx驱动提供了大约70种PostgreSQL类型支持,但还是有一些类型没有涵盖,本文介绍如何自己编写代码支持特殊的类型。
|
6月前
|
Python
在Python中,整型(int)
【4月更文挑战第9天】Python的整型(int)数据类型支持无限大小的整数,包括正数、负数和零。可通过加、减、乘、除、取模和幂运算进行操作。使用`int()`函数可进行类型转换,例如将浮点数转为整数。Python还支持位运算,如按位与、或、异或、取反、左移和右移。整型无固定范围,但大规模数据可能消耗大量内存。注意整数除法会丢失小数部分,浮点数转整数会截断。
72 1
|
6月前
|
存储 编译器 C++
C++系列-第1章顺序结构-4-整型int
C++系列-第1章顺序结构-4-整型int
|
存储 小程序 程序员
8k字详解整型(int)/字符型(char)/浮点型(float)/有符号(signed)/无符号(unsigned)数据在内存中的存储【程序员内功修炼/C语言】
8k字详解整型(int)/字符型(char)/浮点型(float)/有符号(signed)/无符号(unsigned)数据在内存中的存储【程序员内功修炼/C语言】
135 0
|
SQL 存储 Oracle
PostgreSQL技术大讲堂 - 第21讲:行可见性规则
从小白到专家,PostgreSQL技术大讲堂 - 第21讲:行可见性规则
270 1
|
存储 自然语言处理 Java
[oeasy]python0072_整数类型_int_integer_整型变量
[oeasy]python0072_整数类型_int_integer_整型变量
81 0
|
监控 Cloud Native 关系型数据库
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版功能演示(下)——二、功能演示2:增加监控报警规则
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版功能演示(下)——二、功能演示2:增加监控报警规则
|
存储 Java
简自动类型提升,精度损失类型强制转换,常用转义字符,简单帮你回顾Java基本数据类型整形浮点型字符型布尔型Boolean及其运算规则
简自动类型提升,精度损失类型强制转换,常用转义字符,简单帮你回顾Java基本数据类型整形浮点型字符型布尔型Boolean及其运算规则
149 1
简自动类型提升,精度损失类型强制转换,常用转义字符,简单帮你回顾Java基本数据类型整形浮点型字符型布尔型Boolean及其运算规则
|
存储 Java
【剑指offer知识点】Java中无符号整型、如何与int/long类型互相转化
【剑指offer知识点】Java中无符号整型、如何与int/long类型互相转化
416 0
【剑指offer知识点】Java中无符号整型、如何与int/long类型互相转化
|
Go
Golang 字符串([]string)数组转整型([]int)数组
Golang 字符串([]string)数组转整型([]int)数组
429 0

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版