PostgreSQL 自动（隐式）类型转换，解决类型不匹配报错问题

PostgreSQL 自动（隐式）类型转换，解决类型不匹配报错问题

来自博主的笔记：https://blog.csdn.net/qq_43458533/article/details/119521047

 

分类专栏： PostgreSQL 文章标签： 数据库 java
版权

华为云开发者联盟
该内容已被华为云开发者联盟社区收录
加入社区

PostgreSQL
专栏收录该内容
2 篇文章0 订阅
订阅专栏
前言：
最近公司开始大批量的去Oracle转用pg，但是大家都知道pg数据库是个强类型的数据库，数据类型不匹配赋值和比较都会报错，这时候首先要考虑的就是数据结构设计的是否合理，优先应该使自己的表结构符合SQL规范。那么如果确实因为不可抗力的因素必须要使用不同类型的值去比较的话怎么办呢？下面给大家介绍两种解决办法。

第一种解决办法：函数
熟悉PG数据库的朋友应该都清楚要想解决类型不匹配的问题有一个比较简单地办法，那就是用PG的函数去进行类型的强转，例如我们有表a和表b,表a有int8类型的字段a1 ，表b有varchar类型的字段b1，此时如果想通过这两个字段的比较去匹配查询的话就要使用PG提供的函数，如下

a.a1 = b.b1::int8 或者 a.a1::varchar = b.b1
1
这种方法虽然可以解决类型不一致的问题，但是有一个问题很明显，那就是它是需要改动SQL语句，显式的进行类型的转换，如果在不能或者无法改动SQL语句并且不能改动数据库表结构的情况下应该怎么处理呢？

第二种解决方法:隐式类型自动转换
如果有接触过其他数据库，诸如MySQL、Oracle等都是默认对数据类型进行了隐式的转换，在其他数据库varchar等字符串类型和数字可以进行自动的隐式转换，但是PG确没有这么处理，那么有没有办法让PG也实现这个功能呢？答案是肯定的，经过查看官方提供的文档，发现可以通过PG的自定义类型转换定义自己想要的隐式类型转换，下面给大家提供一个简单地示例具体的应用可以去查看官方的文档，场景还是上面的简单场景。

--创建类型转换
--注:创建cast需要有pg_cast系统表的权限
--注:当创建类型转换使用自动隐式转换的话如果出现多个匹配的转换此时pg会因为不知道选择哪一个去处理类型转换而报错，
--如果出现多个隐式自动转换都匹配此时还是需要手动添加转换以达到效果，或者删除多余的类型转换
CREATE CAST (INTEGER AS VARCHAR) WITH INOUT AS IMPLICIT;
CREATE CAST (VARCHAR AS INTEGER) WITH INOUT AS IMPLICIT;
CREATE CAST (BIGINT AS VARCHAR) WITH INOUT AS IMPLICIT;
CREATE CAST (VARCHAR AS BIGINT) WITH INOUT AS IMPLICIT;
1
2
3
4
5
6
7
8
如果想查看自己创建的cast可以用下面的SQL查看

--查询当前的类型转换
--这个查询是当前所有的CAST，具体字段的定义同样可以参阅PG数据库官方文档里的描述
select
(select typname from pg_type where oid = t.castsource) as "castsource",
(select typname from pg_type where oid = t.casttarget) as "casttarget",
castcontext,
castmethod
from pg_cast as t
1
2
3
4
5
6
7
8
删除

--删除类型转换
DROP CAST (varchar as bigint);
DROP CAST (bigint as varchar);
1
2
3
希望上面的解决办法可以解决大家的问题，如果有瑕疵的话希望大佬们指正

下面贴上对应的参考文档地址

pg_cast 表的说明

参考类型转换

原文链接：https://blog.csdn.net/qq_43458533/article/details/119521047