在oracle中，我们如果对两个字符串类型的数字进行加减之类的运算，oracle可以自动将其转换成数值然后进行运算(估计有很多人也碰到过因为这个隐式转换导致查询不走索引之类的问题。。。)。 例如：
SQL> select '2' - '1' from dual; '2'-'1' ---------- 1但是在pg中是不会自动进行转换的，我们查看数据库中默认的cast，可以发现没有text或者varchar到numeric的转换的。
bill=# \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 | 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 ...正因为如此，我们对vachar和text类型的数值进行运算会报错。
bill=# select '2'::varchar - '1'::varchar; ERROR: operator does not exist: character varying - character varying LINE 1: select '2'::varchar - '1'::varchar; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. bill=# select '2'::text - '1'::text; ERROR: operator does not exist: text - text LINE 1: select '2'::text - '1'::text; ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts.为了兼容oracle这一功能，我们可以通过创建相应的隐式转换实现，或者我们也可以创建自定义的操作符实现。
语法：
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 ]创建text到mumeric的cast：
bill=# create cast (text as numeric) with inout AS IMPLICIT ; CREATE CAST再次测试发现，对于text类型数据会自动进行转换了：
bill=# select '2'::text - '1'::text; ?column? ---------- 1 (1 row)可以看到，结果已转换成numeric类型：
bill=# select pg_typeof('2'::text - '1'::text); pg_typeof ----------- numeric (1 row)pg中我们可以对不同数据类型创建新的操作符来实现一系列运算。其实现过程大致为：先创建对应的函数，然后使用该函数创建操作符。详细可以参考：PostgreSQL自定义操作符
创建运算函数：
bill=# create or replace function text_text(text,text) returns numeric as $$ bill$# select $1::numeric-$2::numeric; bill$# $$ language sql strict immutable; CREATE FUNCTION使用该函数创建运算符：
bill=# create operator - (procedure=text_text, leftarg=text, rightarg=text); CREATE OPERATOR测试前先把刚刚创建的cast删除：
bill=# drop cast (text as numeric); DROP CAST测试：
bill=# select '2'::text - '1'::text; ?column? ---------- 1 (1 row) bill=# select pg_typeof('2'::text - '1'::text); pg_typeof ----------- numeric (1 row)参考链接： https://www.postgresql.org/docs/12/sql-createcast.html
