记一次cast使用问题

适用范围

PostgreSQL

问题概述

昨天开发请求一个问题，在PostgreSQL数据库中创建了CAST(numeric as varchar)，但是在进行模糊查询时，抛出如下错误：

ERROR:  operator does not exist: integer ~~ unknown
LINE 1: select * from t1 where id like '%1%';
                                  ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

问题分析

创建测试数据

create table t1 (id numeric);
insert into t1 values (123);

检查开发执行的 cast 创建语句并无问题。

create cast(numeric as varchar) with inout as implicit;

并且查询pg_cast 视图也查到了创建的 cast。

select oid, castsource::regtype, casttarget::regtype, castcontext, castmethod 
from pg_cast
where castsource::regtype='numeric'::regtype
and casttarget::regtype='varchar'::regtype;

怀疑语法有问题，遂通过显式转换检查是否能查出数据：

显然通过显式转换查询并无问题，通过 explain 查看其执行计划：

通过执行计划，可以看到 id 字段做了两次的类型转换，然后再进行模糊查询，那么有理由怀疑PG的优化器无法做到两次类型隐士转换，可以通过创建 numeric as text 的 cast 进行测试验证。

创建cast(numeric as text) 后，无查询语法错误。

解决方案

numeric 类型字段想要使用模糊查询，可以创建 cast(numeric as text)。

create cast(numeric as text) with inout as implicit;

tips:
数据库的类型转换也是需要消耗一定的算力资源，数据库数据量大并且单表很大的情况下，不太建议直接创建 cast 来实现模糊查询。

参考

https://www.postgresql.org/docs/12/sql-createcast.html
https://www.postgresql.org/docs/12/catalog-pg-cast.html
https://developer.aliyun.com/article/228271