数据库自动类型转换

一、错误案例

1、赋值错误：

Caused by: com.highgo.jdbc.util.PSQLException: ERROR: column "updater" is of type double precision but expression is of type character varying

com.highgo.jdbc.util.PSQLException: 错误: 字段 "updater" 的类型为 double precision, 但表达式的类型为 character varying 建议：你需要重写或转换表达式 

解决方法：

create cast(varchar as double) with inout as ASSIGNMENT;

2、查询错误：

Cause: com.highgo.jdbc.util.PsQLException: ERROR: operator does not exist: bit = boolean建议: No operator matches the given name and argument types. You might need to add explicit type casts.
org.postgresql.util.PSQLException: 错误: 操作符不存在: bit = boolean
建议：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.

解决方法：

-- operator does not exist: bit = boolean
-- sysdba用户在应用连接的数据库执行
-- 1、创建转换函数
create or replace function boo_cast_bit(boolean) returns bit as $$
begin
if $1=true
then return 1;
else return 0;
end if;
end;
$$
language plpgsql;
-- 2、创建自动类型转换并使用该转换函数
create cast(boolean as bit) with function boo_cast_bit as implicit;

二、知识点总结

瀚高 数据类型直接有3种转换，隐式转换，赋值转换，显式转换；

对应的转换类型存在系统表 pg_cast中
三种方式分别对应 i（Implicit），a（Assignment），e（Explicit）

查询所有转换：

SELECT 
    c.castsource::regtype AS source_type,
    c.casttarget::regtype AS target_type,
    c.castfunc::regproc AS cast_function,
    c.castcontext
FROM 
    pg_cast c 
where   c.castsource::regtype = 'bit'::regtype or c.casttarget::regtype = 'bit'::regtype;

三者的转换关系为 i > a > e；意思是可以隐式转换的一定可以赋值转换和显式转换；可以赋值转换的一定可以显式转换

可以显式转换的不能隐式转换和赋值转换。i：主要用户表达式和赋值，a主要用于赋值。

相关文章：

https://blog.csdn.net/qq_39727113/article/details/105371412

三、pg_cast中有的可以进行update，如果没有的可以create新的cast

实例1：自动将 bit 转换为 boolean

   如果没有内置的转换函数，我们可能需要自定义转换函数来支持这种转换。(如果不需要转换函数：WITHOUT FUNCTION)

-- 创建转换函数 function
create or replace function boo_cast_bit(boolean) returns bit as $$
begin
if $1=true
then return 1;
else return 0;
end if;
end;
$$
language plpgsql;
-- 创建 cast 规则
create cast(boolean as bit) with function boo_cast_bit as implicit;

 实例2、自动将bit转成integer

--这行创建cast会报错：类型 integer 到 bit 的转换已经存在，因此这行执行不了，只能进行update
create cast(integer as bit) with inout as implicit;
-- 使用update进行修改
update pg_cast set castcontext='i' where castsource ='integer'::regtype and casttarget='bit'::regtype;

四、cast创建语法

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 ]

解释：

1、WITH FUNCTION，表示转换需要用到什么函数。

2、WITHOUT FUNCTION，表示被转换的两个类型，在数据库的存储中一致，即物理存储一致。例如text和varchar的物理存储一致。不需要转换函数。

3、WITH INOUT，表示使用内置的IO函数进行转换。每一种类型，都有INPUT 和OUTPUT函数。使用这种方法，好处是不需要重新写转换函数。

除非有特殊需求，建议直接使用IO函数来进行转换。

4、AS ASSIGNMENT，表示在赋值时，自动对类型进行转换。例如字段类型为TEXT，输入的类型为INT，那么可以创建一个 cast(int as text) as ASSIGNMENT。

5、AS IMPLICIT，表示在表达式中，或者在赋值操作中，都对类型进行自动转换。（包含了AS ASSIGNMENT，它只对赋值进行转换）

6、注意，AS IMPLICIT需要谨慎使用，为什么呢？因为操作符会涉及到多个算子，如果有多个转换，目前数据库并不知道应该选择哪个就会报错

建议：

1、慎用AS IMPLICIT。

2、慎用自动类型转换，因为过多不合理的类型转换之前可能会有冲突，排查难度大。

3、建议使用AS IMPLICIT的CAST应该是非失真转换转换，例如从INT转换为TEXT，或者int转换为numeric。而失真转换，不建议使用as implicit，例如numeric转换为int。