PostgreSQL类型转换问题处理思路
⽬录
概述
在PostgreSQL中，默认在进⾏数据操作及运算时，需要类型统⼀才能完美操作。当不同类型之间进⾏操作时，会产⽣类型⾃动兼容问题。下⾯就来介绍⼀下错误场景及解决⽅案。
1.1问题分类
在类型⾃动兼容问题中，我主要将其分为两⼤类：
第⼀类：在进⾏inrt时所产⽣的问题，⼀般报错为
column "col" is of type type1 but expression is of type type2
第⼆类：是进⾏运算时所产⽣的问题，⼀般报错为
operator does not exist: type1 = type2
对于不同类问题有不同的解决思路，当然，也存在同⼀思路就⾏解决两类相关问题，这个需要⼤家⾃⼰仔细研究⼀下，就可以摸索出其中的规律。
1.2解决思路
⾸先排查⼀下，数据库中是否已经存在转换关系，⽐如boolean to int
如上，数据库中已存在该转换功能，状态为未启⽤
查询库中是否存在两种类型的强制转换关系
SELECT OID,castsource , (SELECT typname FROM pg_type WHERE oid = castsource) AS castsourcename ,
casttarget , (SELECT typname FROM pg_type WHERE oid = casttarget) AS casttargetname ,
castfunc , (SELECT proname FROM pg_proc WHERE oid = castfunc ) AS castfuncname,
castcontext ,decode(castcontext::text , 'e'::text , '禁⽌'::text , 'a'::text , '赋值'::text , 'i'::text , '全部'::text )
FROM pg_cast
WHERE castsource IN( SELECT oid FROM pg_type WHERE typname LIKE '%bool%' )
AND casttarget IN( SELECT oid FROM pg_type WHERE typname LIKE '%int%' );
oid | castsource | castsourcename | casttarget | casttargetname | castfunc | castfuncname | castcontext | decode
-------+------------+----------------+------------+----------------+----------+--------------+-------------+--------
11299 | 16 | bool | 23 | int4 | 2558 | int4 | e | 禁⽌
(1 ⾏记录)
castsource ：源数据类型oid
castsourcename : 源数据类型名称
casttarget : ⽬标类型oid
casttargetname ：⽬标类型名称
castfunc : 类型转换函数 oid
castfuncname : 类型转换函数名称
castcontext ： 该类型转换功能状态标识 e(禁⽌)、a(赋值)、i(全部，包括赋值)
如上，该强制转换状态为禁⽌，更新已有强制转换关系的状态，修改为赋值久久资源365
UPDATE pg_cast SET castcontext = 'a' WHERE castsource = 16 AND casttarget = 23;
如果数据库中没有存在相应的强制转换，则需要⼿动创建，参考如下⽅法。
第⼀类问题：inrt引发
该类问题分两种情况进⾏解决
国际象棋棋盘图第⼀种：单纯的inrt值类型不匹对
问题描述：该类问题可以通过create cast(type1 as type2)解决，⽐如：column "code" is of type numeric but expression is of type character varying
解决⽅案：create cast(varchar as numeric) with inout as implicit
⽰例：
db1=# create table t_varchar_to_numeric(id rial,code numeric);
CREATE TABLE
db1=# inrt into t_varchar_to_numeric(code) values('123'::varchar);
ERROR: 42804: column "code" is of type numeric but expression is of type character varying
第1⾏inrt into t_varchar_to_numeric(code) values('123'::
^
提⽰: You will need to rewrite or cast the expression.
db1=#create CAST(varchar as numeric) with inout as implicit;
CREATE CAST
db1=# inrt into t_varchar_to_numeric(code) values('123'::varchar);
INSERT 0 1
db1=# lect * from t_varchar_to_numeric;
id | code
----+------
1 | 123
总价(1 ⾏记录)
第⼆种：需要对inrt值做处理
问题描述：⽐如，inrt boolean类型的值到numeric类型列中，需要对值进⾏处理，由于inrt时在HighGoDB中默认true值为t，fal 的值为f，⽽numeric类型⽆法直接接收t/f，需要将其处理为1/0。
这个时候我们需要⾃定义⼀个类型转换调⽤的函数，在create cast(boolean as numeric)时指定调⽤⾃定义函数进⾏转换。假设函数为cast_boolean2numeric(boolean)。
解决⽅案：
create or replace function cast_boolean2numeric (boolean) returns numeric as
$$
lect decode($1::boolean,'f'::boolean,0::numeric,'t'::boolean,1::numeric,true);
她是我的宝贝$$
language sql strict; --⾃定义转换函数
create cast(boolean as numeric) with function cast_boolean2numeric(boolean) as implicit;
下厨⽰例：
db1=# create table t_boolean_to_numeric(id rial,ifcode numeric);
CREATE TABLE
db1=# inrt into t_boolean_to_numeric(ifcode) values(true::boolean);
ERROR: 42804: column "ifcode" is of type numeric but expression is of type boolean
第1⾏inrt into t_boolean_to_numeric(ifcode) values(true::
^
提⽰: You will need to rewrite or cast the expression
db1=# create or replace function cast_boolean2numeric (boolean) returns numeric as
db1-#$$
db1$#lect decode($1::boolean,'f'::boolean,0::numeric,'t'::boolean,1::numeric,true);
db1$# $$
db1-#language sql strict;
CREATE FUNCTION
db1=# create cast(boolean as numeric) with function cast_boolean2numeric(boolean) as implicit;
二噁英CREATE CAST
db1=# inrt into t_boolean_to_numeric(ifcode) values(true::boolean);
INSERT 0 1
db1=# lect * from t_boolean_to_numeric;
id | ifcode
----+--------
1 | 1
(1 ⾏记录)
如果直接使⽤解决第⼀种问题的⽅式来处理此问题，则报错
db1=# create cast(boolean as numeric) with inout as implicit;
过热CREATE CAST
db1=# inrt into t_boolean_to_numeric(ifcode) values(true::boole
ERROR: 22P02: invalid input syntax for type numeric: "t"
第⼆类问题：操作符引发
查看数据库中是否存在操作符
lect oprname,oprleft ,(lect typname from pg_type where oid = oprleft) as lefttype, oprright,(lect typname from pg_type where oid = oprleft) as righttype,oprcode
from pg_operator
where oprleft in (lect oid from pg_type where typname like '%char%')
and oprright in (lect oid from pg_type where typname like '%bool%')
and oprname = '='；
oprname ：操作符名称
oprleft ：左参数数据类型
oprright : 右参数数据类型
oprcode : 操作符运算函数名
问题描述：操作引发的问题就是报某某操作符不存在：operator does not exist: type1 = type2如：operator does not exist: character=boolean
解决⽅案：根据报错的操作符详细信息⾃定义操作符进⾏解决(CREATE OPERATOR)。CREATE FUNCTION char_equal_boolean(char,boolean) RETURNS boolean
AS
LANGUAGE SQL; --⾃定义操作符调⽤的函数
CREATE OPERATOR =(
PROCEDURE = char_equal_boolean,
LEFTARG = char,
RIGHTARG = boolean);
COMMENT ON OPERATOR =( char, boolean) IS ' char equals boolean ';
⽰例：
db1=# lect '1'::char = true::boolean;
ERROR: 42883: operator does not exist: character = boolean
第1⾏lect '1'::char = true::boolean;
^
提⽰: No operator matches the given name and argument type(s). You might need to add explicit type casts. db1=# CREATE FUNCTION char_equal_boolean(char,boolean) RETURNS boolean
db1-# AS
db1-# LANGUAGE SQL;
CREATE FUNC
db1=#CREATE OPERATOR =(
db1(# PROCEDURE = char_equal_boolean,
db1(#LEFTARG = char,梯形中位线
db1(# RIGHTARG = boolean);
CREATE OPERATOR
db1=#COMMENT ON OPERATOR =( char, boolean) IS ' char equals boolean ';
COMMENT
db1=# lect '1'::char = true::boolean;
column
-
---------
t
(1 ⾏记录)
