PostgreSQL小知识：column "..." does not exist、字符串转整数integer、sql排查错误

一、ERROR: column "..." does not exist

　　在PostgreSQL中，不论是在pgAdmin中，还是在命令行控制台里面，在SQL语句中表示属性值的总会遇到ERROR: column "..." does not exist这样的错误，比如下面的语句：

　　解决方案：将 "27" 的双引号改成 单引号 就行了。

　　问题原因：可能是被双引号括起来的，PostgreSQL都会认为是“名称”，如表名，字段名等，而被单引号括起来的就表示值了。

　　可以对比下，颜色都不一样。所以在MyBatis里遇到这个问题，估计也是双引号的原因。

二、字符串转整数 integer

<if test="dbid != null">and (dbid = #{dbid} or id = cast(#{dbid} as integer))</if>

1、字符串转数字类型

// 把'1234'转成整数
select cast('1234' as integer ) ;

// 用substring截取字符串，从第8个字符开始截取2个字符：结果是12
select cast(substring('1234abc12',8,2) as integer)

2、把数字类型与字符类型比较

// 进行转换：cast(classify.ad_org_id as char(4))
glt0.bukrs=cast(classify.ad_org_id as char(4))

三、SQL排查错误

1、报错：Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer

　　报错：Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer

　　Springboot项目，使用postgresql数据库，mybatis做持久层框架，当mybatis里有 and com_code=${comCode}，comcode的形式如：'1100'，但是当传的不是 varchar 就会报错，如下

SQL: select id, comcode, name from test where state='2' and com_code = 00000000
Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = integer

　　需要转换下，解决方法：

<if test="comCode != null" >
    and com_code=#{comCode, jdbcType=VARCHAR}
</if>

　　还有一种就是数据库2个字段类型不一致，比如  a.rid = b.base_id 这样，rid 为 6个字符的varchar，而 base_id 为 Integer 时，就会报错。

　　解决方案：需要转换一下，有 2 种方式

a.rid = b.base_id::varchar
a.rid = cast(b.base_id as varchar)

2、报错：Caused by: org.postgresql.util.PSQLException: ERROR: argument of CASE/WHEN must be type boolean, not type integer

-- 错误
CASE WHEN l.id THEN true ELSE false END isLiked

-- 修改正确
CASE WHEN l.id is not null THEN true ELSE false END isLiked

3、报错：org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying

-- 错误
LEFT JOIN op_like l ON c.id = l.rid AND l.type = 'comment' AND l.created_by = 1093

-- 修改正确
left join op_like l on c.id = l.rid::integer and l.type='comment' and l.created_by=#{userId}

-- 错误
or b.created_by in (select rid from op_follow where type = 'user' and created_by = 1093)

-- 修改正确
or b.created_by in (select rid::integer from op_follow where type = 'user' and created_by = 1093)