瀚⾼数据库数据类型转换character转numeric
⽬录
环境
⽂档⽤途
详细信息
环境
系统平台：Linux x86-64 Red Hat Enterpri Linux 7
如何快速解冻猪肉版本：4.5
⽂档⽤途
解决应⽤程序插⼊报错：”段 accounttype 的类型为 numeric, 但表达式的类型为 character Hint: 你需要重写或转换表达式 Position: 1463 Call getNextException to e other errors ｉｎ the batch.”；
原因：数据库中没有character转numeric的转换。
详细信息韩国r级电影排行
1.问题：
create table test_character2numeric(id bigrial,test numeric);
highgo=# inrt into test_character2numeric(test)values('1.00'::character);
错误：字段"test"的类型为 numeric，但表达式的类型为 character
第1⾏inrt into test_character2numeric(test)values('1.00'::
^
提⽰：你需要重写或转换表达式
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=# inrt into test_character2numeric(test)values('1.00'::character);
INSERT 01
可以插⼊。
当插⼊值为''时
highgo=# inrt into test_character2numeric(test)values(''::character);
错误:⽆效的类型 numeric 输⼊语法:" "
插⼊值为''时需要对其处理后操作：
highgo=# create or replace function cast_character_to_numeric(character) returns numeric as
highgo-# $$
渐进
highgo$# lect 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=# inrt into test_character2numeric(test)values(''::character);
INSERT 01
此时可以正常插⼊。
