pg 数据转换mysql pg数据类型转换

目录

 

概述

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