瀚⾼数据库数据类型转换character转numeric

⽬录

环境

⽂档⽤途

详细信息

环境

系统平台：Linux x86-64 Red Hat Enterprise Linux 7

版本：4.5

⽂档⽤途

解决应⽤程序插⼊报错：”段 accounttype 的类型为 numeric, 但表达式的类型为 character Hint: 你需要重写或转换表达式 Position: 1463 Call getNextException to see other errors ｉｎ the batch.”；

原因：数据库中没有character转numeric的转换。

详细信息

1.问题：

create table test_character2numeric(id bigserial,test numeric);

highgo=# insert into test_character2numeric(test)values('1.00'::character);

错误:字段"test"的类型为 numeric,但表达式的类型为 character

第1⾏insert into test_character2numeric(test)values('1.00'::cha...

^

提⽰:你需要重写或转换表达式

2.可以看到没有character类型转numeric：

highgo=# \dC+ character

类型转换列表

来源类型|⽬标类型|函数|隐含的?|描述

-------------------+-------------------+--------------------+----------+------

"char"| character | bpchar |在指派中|

boolean | character | text |在指派中|

character |"char"|char|在指派中|

character | character | bpchar |是|

character | character varying | compatible_text |是|

character | name | name |是|

character | nvarchar2 |(binary coercible)|是|

character | text | compatible_text |是|

character | varchar2 |(binary coercible)|是|

character | xml | xml |否|

character varying | character |(binary coercible)|是|

cidr | character | text |在指派中|

inet | character | text |在指派中|

name | character | bpchar |在指派中|

nvarchar2 | character |(binary coercible)|是|

text | character |(binary coercible)|是|

varchar2 | character |(binary coercible)|是|

xml | character |(binary coercible)|在指派中|

(18⾏记录)

3.隐⼠转换语法：

highgo=# \h create cast

Command: CREATE CAST

Description:建⽴新的类型转换

Syntax:

CREATE CAST(类型指派中的源数据类型 AS 类型指派中的⽬标数据类型)

WITH FUNCTION 函数名称[(参数类型[,...])]

[ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST(类型指派中的源数据类型 AS 类型指派中的⽬标数据类型)

WITHOUT FUNCTION

[ AS ASSIGNMENT | AS IMPLICIT ]

CREATE CAST(类型指派中的源数据类型 AS 类型指派中的⽬标数据类型)

WITH INOUT

[ AS ASSIGNMENT | AS IMPLICIT ]

4.创建隐⼠转换：

highgo=# create cast(character as numeric) with inout as implicit;

CREATE CAST

测试：

highgo=# insert into test_character2numeric(test)values('1.00'::character);

INSERT 01

可以插⼊。

当插⼊值为''时

highgo=# insert into test_character2numeric(test)values(''::character);

错误:⽆效的类型 numeric 输⼊语法:" "

插⼊值为''时需要对其处理后操作：

highgo=# create or replace function cast_character_to_numeric(character) returns numeric as

highgo-# $$

highgo$# select to_number(decode($1::character,''::character,null,$1::character));

highgo$# $$

highgo-# language sql strict;

CREATE FUNCTION

highgo=# create cast(character as numeric) with function cast_character_to_numeric(character) as implicit;错误:类型 character 到 numeric 的转换已经存在

highgo=#

需要删除之前的转换再进⾏创建

highgo=# drop cast(character as numeric);

DROP CAST

highgo=# create cast(character as numeric) with function cast_character_to_numeric(character) as implicit; CREATE CAST

highgo=# insert into test_character2numeric(test)values(''::character);

INSERT 01

此时可以正常插⼊。