PostgreSQL类型转换问题处理思路
概述在PostgreSQL中,默认在进行数据操作及运算时,需要类型统一才能完美操作。当不同类型之间进行操作时,会产生类型自动兼容问题。下面就来介绍一下错误场景及解决方案。1问题分类在类型自动兼容问题中,我主要将其分为两大类:第一类:在进行insert时所产生的问题,一般报错为column "col" is of type type1 but expression i...
目录
概述
在PostgreSQL中,默认在进行数据操作及运算时,需要类型统一才能完美操作。当不同类型之间进行操作时,会产生类型自动兼容问题。下面就来介绍一下错误场景及解决方案。
1.1问题分类
在类型自动兼容问题中,我主要将其分为两大类:
第一类:在进行insert时所产生的问题,一般报错为
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(全部,包括赋值)
如上,该强制转换状态为禁止,更新已有强制转换关系的状态,修改为赋值
UPDATE pg_cast SET castcontext = 'a' WHERE castsource = 16 AND casttarget = 23;
如果数据库中没有存在相应的强制转换,则需要手动创建,参考如下方法。
第一类问题:insert引发
该类问题分两种情况进行解决
第一种:单纯的insert值类型不匹对
问题描述:该类问题可以通过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 serial,code numeric);
CREATE TABLE
db1=# insert 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行insert into t_varchar_to_numeric(code) values('123'::varchar...
^
提示: You will need to rewrite or cast the expression.
db1=# create CAST(varchar as numeric) with inout as implicit;
CREATE CAST
db1=# insert into t_varchar_to_numeric(code) values('123'::varchar);
INSERT 0 1
db1=# select * from t_varchar_to_numeric;
id | code
----+------
1 | 123
(1 行记录)
第二种:需要对insert值做处理
问题描述:比如,insert boolean类型的值到numeric类型列中,需要对值进行处理,由于insert时在HighGoDB中默认true值为t,false的值为f,而numeric类型无法直接接收t/f,需要将其处理为1/0。
这个时候我们需要自定义一个类型转换调用的函数,在create cast(boolean as numeric)时指定调用自定义函数进行转换。假设函数为cast_boolean2numeric(boolean)。
解决方案:
create or replace function cast_boolean2numeric (boolean) returns numeric as
$$
select 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 serial,ifcode numeric);
CREATE TABLE
db1=# insert into t_boolean_to_numeric(ifcode) values(true::boolean);
ERROR: 42804: column "ifcode" is of type numeric but expression is of type boolean
第1行insert into t_boolean_to_numeric(ifcode) values(true::boolea...
^
提示: You will need to rewrite or cast the expression.
db1=# create or replace function cast_boolean2numeric (boolean) returns numeric as
db1-# $$
db1$# select 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=# insert into t_boolean_to_numeric(ifcode) values(true::boolean);
INSERT 0 1
db1=# select * from t_boolean_to_numeric;
id | ifcode
----+--------
1 | 1
(1 行记录)
如果直接使用解决第一种问题的方式来处理此问题,则报错
db1=# create cast(boolean as numeric) with inout as implicit;
CREATE CAST
db1=# insert into t_boolean_to_numeric(ifcode) values(true::boolean);
ERROR: 22P02: invalid input syntax for type numeric: "t"
第二类问题:操作符引发
查看数据库中是否存在操作符
select oprname,oprleft ,(select typname from pg_type where oid = oprleft) as lefttype,
oprright,(select typname from pg_type where oid = oprleft) as righttype,oprcode
from pg_operator
where oprleft in (select oid from pg_type where typname like '%char%')
and oprright in (select 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 $$ SELECT $1::boolean = $2::boolean $$
LANGUAGE SQL; --自定义操作符调用的函数
CREATE OPERATOR =(
PROCEDURE = char_equal_boolean,
LEFTARG = char,
RIGHTARG = boolean);
COMMENT ON OPERATOR =( char, boolean) IS ' char equals boolean ';
示例:
db1=# select '1'::char = true::boolean;
ERROR: 42883: operator does not exist: character = boolean
第1行select '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 $$ SELECT $1::boolean = $2::boolean $$
db1-# LANGUAGE SQL;
CREATE FUNCTION
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=# select '1'::char = true::boolean;
?column?
----------
t
(1 行记录)
开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!
更多推荐
所有评论(0)