PostgreSQL Oracle兼容性之 - text - text隐式转换

背景

Oracle的两个文本详见，会自动转换为数值进行相减操作。

PostgreSQL默认并不会将文本转换为数值进行相减操作。

为了实现兼容，有两种方法：

1、创建text到numeric的隐式转换

2、创建text-text的操作符。

PostgreSQL 内置CAST

可以看到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-varchar，可以自动算出结果来。

postgres=#   select '1'::varchar - '2.1'::varchar;  
 ?column?   
----------  
     -1.1  
(1 row)  

但是使用text-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、隐式转换方法

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)  

2、创建text-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-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)  

参考

《PostgreSQL 整型int与布尔boolean的自动转换设置(含自定义cast与cast规则介绍)》

《PostgreSQL 自定义自动类型转换(CAST)》

digoal’s 大量PostgreSQL文章入口