五月 16, 2023
Oracle 的两个文本类型的数值相减，会自动转换为数值进行相减操作。
PostgreSQL 默认并不会将文本转换为数值进行相减操作。
为了实现兼容，有两种方案：
1、定义 text 类型到 numeric 类型的隐式转换规则
2、创建 text 类型相减操作符。
PostgreSQL 类型转换规则
可以看到 varchar 是有隐式转 numeric 的。但是 text 类型没有隐式转 numeric。
postgres=# \dC
List of casts
Source type | Target type | Function | Implicit?
-----------------------------+-----------------------------+---------------------------+---------------
...........
character varying | "char" | char | in assignment
character varying | character | (binary coercible) | yes
character varying | character varying | varchar | yes
character varying | name | name | yes
character varying | numeric | (binary coercible) | yes
character varying | regclass | regclass | yes
character varying | text | (binary coercible) | yes
character varying | xml | xml | no
..........
text | "char" | char | in assignment
text | character | (binary coercible) | yes
text | character varying | (binary coercible) | yes
text | name | name | yes
text | regclass | regclass | yes
text | xml | xml | no
...........
(241 rows)
因此如果你使用的是 varchar 类型的数值相减，可以自动算出结果来。
postgres=# select '1'::varchar - '2.1'::varchar;
?column?
----------
-1.1
(1 row)
但是使用 text 类型的数值相减就得不到结果。
postgres=# select '1'::text - '2'::text;
ERROR: operator does not exist: text - text
LINE 1: select '1'::text - '2'::text;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
方案一：隐式转换方法
1、创建隐式转换的语法
postgres=# \h create cast
Command: CREATE CAST
Description: define a new cast
Syntax:
CREATE CAST (source_type AS target_type)
WITH FUNCTION function_name [ (argument_type [, ...]) ]
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITHOUT FUNCTION
[ AS ASSIGNMENT | AS IMPLICIT ]
CREATE CAST (source_type AS target_type)
WITH INOUT
[ AS ASSIGNMENT | AS IMPLICIT ]
2、创建 text 隐式转 numeric
postgres=# create cast (text as numeric) with inout AS IMPLICIT ;
CREATE CAST
3、现在可以做减法了
postgres=# select '1'::text - '2'::text;
?column?
----------
-1
(1 row)
postgres=# select '1'::text - '2.1'::text;
?column?
----------
-1.1
(1 row)
方案二：创建 text 类型相减操作符方法
第二种方法是使用新建操作符的方法。
1、创建运算函数
create or replace function text_text(text,text) returns numeric as $$
select $1::numeric-$2::numeric;
$$ language sql strict immutable;
CREATE FUNCTION
测试运算函数
postgres=# select text_text('1.1', '2.222');
text_text
-----------
-1.122
(1 row)
2、基于运算函数，创建操作符
postgres=# create operator - (procedure=text_text, leftarg=text, rightarg=text);
CREATE OPERATOR
3、现在可以支持 text 类型相减操作了。
为了验证操作符的效果，先把前面创建的隐式转换删掉
postgres=# drop cast (text as numeric);
DROP CAST
postgres=# select '1.1'::text-'1.2'::text;
?column?
----------
-0.1
(1 row)
postgres=# select '1.1'::text-'1.22'::text;
?column?
----------
-0.12
(1 row)