PG数据库 column “has_submit_am“ is of type numeric but expression is of type bool

目录

场景：

现象：

 复盘分析：

解决方法：

测试：

扩展：

---

场景：

今天遇到一个问题，现场数据库中做了boolean隐式转换smallint时在执行对应的插入时一直报错

column "has_submit_am" is of type numeric but expression is of type bool花费了许多时间总结一下

现象：

pg数据库查询表中某个字段类型

SELECT data_type FROM information_schema.columns WHERE table_catalog='bdcpz' and table_schema='bdcycsl' 
and table_name='t_xm_extend'
AND column_name = 'has_submit_am';

 复盘分析：

第一：由于历史原因项目中很多地方插入数据库都是boolean类型，但是数据库中相应字段数字类型，在pg数据库是做了boolean_to_smallint函数的隐式转化，因此没遇到问题。

CREATE OR REPLACE FUNCTION boolean_to_smallint(b boolean) RETURNS smallint AS $$
    BEGIN
            RETURN (b::boolean)::bool::int;
    END;
$$LANGUAGE plpgsql;

CREATE CAST (boolean AS smallint) WITH FUNCTION boolean_to_smallint(boolean) AS implicit;

 第二：现场环境由于是从mysql数据直接导出pg类型的数据，所以和公司的表结构并不是完全一致的。比如公司定义生成has_submit_am字段类型是smallint，然而现场迁移出的数据该字段变成了numeric，因此在插入的时候一直报错column "has_submit_am" is of type numeric but expression is of type boolean

第三：自己也陷入了误区smallint和numeric认为都是数字类型，误认为boolean_to_smallint的这种转化也适用于boolean自动转化为numeric

解决方法：

再定义一个boolean自动转化成numeric的隐式转化函数boolean_to_numeric

CREATE OR REPLACE FUNCTION boolean_to_numeric(b boolean) RETURNS numeric AS $$
    BEGIN
            RETURN (b::boolean)::bool::int;
    END;
$$LANGUAGE plpgsql;

CREATE CAST (boolean AS numeric) WITH FUNCTION boolean_to_numeric(boolean) AS implicit;

测试：

扩展：

删除隐式转化：

DROP CAST IF EXISTS (boolean AS smallint);

 删除函数：

DROP FUNCTION [IF EXISTS] function_name ([argument_list]);