开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

标签

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)》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
postgresql如何设置自动增长
postgresql如何设置自动增长
71 0
RISC-V MCU开发 (十一):跨内核工程转换
大多数嵌入式工程师使用Keil进行开发,但Keil目前不支持RISC-V内核,只支持ARM内核。MounRiver® Studio(MRS)同时支持两种内核,为了方便工程师从ARM内核迁移至RISC-V内核,MRS增加了Keil工程转换的功能。
66 0
JAVA Swing自定义JScorllPanel
JAVA Swing中JScrollPanle自定义带标尺的JScrollPanel Swing中JScrollPanel的结构与组成如下图: 分为四个角与四个边,中间蓝色矩形为View窗口,可以通过调用JScrollPanel的 getViewport().setView(view)完成对现实内容的设置。
742 0
Ext.NET之动态绑定GridPanel
经常会有朋友问如何动态绑定GridPanel,由于一直很忙,这次索性发帖来统一回答。 使用过Ext.NET的都知道,Ext.NET中的控件GridPanel很强大,也很好用,可以实现各种功能,但是相比GridView,比较欠缺的是,GridPanel无法自动绑定列与数据。
818 0
ASP.NET MVC 重点教程一周年版 第八回 Helper之演化
凡事出现必有原因 就像Filter,它是为了解决在一类的Action之前或之后执行统一的代码而产生的。 而Helper则是为了方便View的开发而产生的。 下面我们来解决几个问题,来看看Helper是怎么演化出来的。
810 0
一起谈.NET技术,Asp.net mvc 3 beta 新特性介绍
  国庆放假归来,刚好赶上asp.net mvc 3 beta发布,和大家分享点我的体验。   首先是创建项目时的选择界面的改变:   1.View Engine的变化。   asp.net mvc 3中添加了Razor这个View engine。
1119 0
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
2156
文章
245
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载