PostgreSQL类型转换问题处理思路

目录

 

概述

1.1问题分类

1.2解决思路

第一类问题：insert引发

第一种：单纯的insert值类型不匹对

第二种：需要对insert值做处理

第二类问题：操作符引发

---

概述

     在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 行记录)