迁移 Oracle 到 PostgreSQL: text 类型隐式转换到数值类型

五月 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)