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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

标签

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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
6月前
|
SQL 流计算 OceanBase
OceanBase CDC从热OB库采集过来的Tinyint(1)类型会默认转换成Boolean,请教一下,如果想转换成int类型,有什方法么?
【2月更文挑战第25天】OceanBase CDC从热OB库采集过来的Tinyint(1)类型会默认转换成Boolean,请教一下,如果想转换成int类型,有什方法么?
173 3
|
关系型数据库 Go PostgreSQL
golang pgx自定义PostgreSQL类型
golang的pgx驱动提供了大约70种PostgreSQL类型支持,但还是有一些类型没有涵盖,本文介绍如何自己编写代码支持特殊的类型。
|
关系型数据库 PostgreSQL
PostgreSQL错误: 操作符不存在: smallint = boolean
PostgreSQL错误: 操作符不存在: smallint = boolean
240 0
MybatisPlus-标准CRUD制作,新增boolean save(T t),删除 ~ delete(int id),修改 ~ update(T t),根据id查询,T getById....
MybatisPlus-标准CRUD制作,新增boolean save(T t),删除 ~ delete(int id),修改 ~ update(T t),根据id查询,T getById....
|
SQL 存储 Oracle
PostgreSQL技术大讲堂 - 第21讲:行可见性规则
从小白到专家,PostgreSQL技术大讲堂 - 第21讲:行可见性规则
271 1
|
监控 Cloud Native 关系型数据库
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版功能演示(下)——二、功能演示2:增加监控报警规则
《阿里云认证的解析与实战-数据仓库ACP认证》——云原生数据仓库AnalyticDB PostgreSQL版功能演示(下)——二、功能演示2:增加监控报警规则
|
存储 Java
简自动类型提升,精度损失类型强制转换,常用转义字符,简单帮你回顾Java基本数据类型整形浮点型字符型布尔型Boolean及其运算规则
简自动类型提升,精度损失类型强制转换,常用转义字符,简单帮你回顾Java基本数据类型整形浮点型字符型布尔型Boolean及其运算规则
150 1
简自动类型提升,精度损失类型强制转换,常用转义字符,简单帮你回顾Java基本数据类型整形浮点型字符型布尔型Boolean及其运算规则
|
Java
java基本数据类型, byte: short: int: long: float: double: float和double有什么区别 boolean: ch
java基本数据类型, byte: short: int: long: float: double: float和double有什么区别 boolean: ch
266 0
|
SQL 监控 关系型数据库
21 PostgreSQL 监控2 趋势监控数据收集和分析 nagios 实时监控部署和自定义监控|学习笔记(三)
快速学习21 PostgreSQL 监控2 趋势监控数据收集和分析 nagios 实时监控部署和自定义监控
300 0
21 PostgreSQL 监控2 趋势监控数据收集和分析 nagios 实时监控部署和自定义监控|学习笔记(三)
|
存储 关系型数据库 PostgreSQL
PostgreSQL 布尔类型
PostgreSQL 布尔类型
332 0

相关产品

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