Postgresql类型自动转换--手动定义类型转换函数

在我们习惯于Oracle的数据类型自动转换功能后，很多时候将原先基于Oracle的应用迁移到Postgresql中时，对于数据类型处理这一块我们需要花费大量的工作。因为Postgresql类型是严格校验的，比如时间与字符类型、布尔与数值类型及数值与字符类型等不能相互运算及赋值。
下面分享几个案例，通过手动自定义转换函数处理以上描述的数据类型字符转换。

1.时间类型与字符类型

解决时间类型与字符类型的赋值，特别是 insert  ‘’ 到时间类型字段的操作，直接insert会报错

如上信息我们可以看到，提示的是shijian字段是date类型，而参数值是varchar类型。
自定义函数解决如下：

create or replace function varchar_to_date(varchar) returns date as
$$
    select to_date(decode($1::text,''::text,null,$1))
    
$$
language sql strict;

create cast(varchar as date) with function text_to_date(varchar)as implicit;

测试上述自定义转换函数

create table t_date(id serial,shijian date);
insert into t_date(shijian) values(''::varchar);
select * from t_date;
select '20190828'::date = '20190828'::varchar
select '20190828'::varchar= '20190828'::date

同理，如果报错是
type date but expression if of type text的话，只需要把自定义函数的varchar类型改为text就好。时间类型有改变也是一样，只需将时间类型更换一下。
综上，我们需要关注报错的两类型，根据这两个类型去创建转换函数

2.boolean类型与数值类型

解决insert时Boolean类型数据无法赋值给numeric类型，特别是Boolean使用true或者false表示时，如
create table t_boolean(id serial,ifval numeric);
insert into t_boolean(ifval) values(false); --执行插入

如上可以看到ifval的类型是数值，而insert的值是boolean，自定义函数如下：

    create or replace function boolean_to_numeric(boolean) returns numeric as 
$$
    select decode($1::boolean,'f'::boolean,0::numeric,'t'::boolean,1::numeric,true);
$$
language sql strict;

create cast(boolean as numeric) with function boolean_to_numeric(boolean) as implicit;

测试如上自定义转换函数

create table t_boolean(id serial,ifval numeric);
insert into t_boolean(ifval) values(false);
select * from t_boolean;
select false = 0;
select 1 = true;

3.数值类型与字符类型

解决insert时varchar类型数据无法赋值给numeric类型，特别是varchar使用 ‘’，如
create table t_numeric(id serial ,num numeric);

insert into t_numeric(num) values('123'::varchar);

如上信息可得num的类型是numeric，而insert的值是varchar类型，自定义函数如下：

    create or replace function varchar_to_numeric(varchar) returns numeric as
    $$
        select to_number(decode($1::text,''::text,null,$1::text));
    $$
    language sql strict 
    
create cast(varchar as numeric) with function varchar_to_numeric(varchar) as implicit;

测试如上自定义转换函数

create table t_numeric(id serial ,num numeric);
insert into t_numeric(num) values('123'::varchar);
insert into t_numeric(num) values(''::varchar);
select '123'::varchar = 123::numeric;
select '123'::numeric = 123::varchar;

                                                              2019.8.29 
                                                               by 谷焱