当前位置：首页 > news >正文

postgresql查询时报类型错误解决办法

news 来源：原创 2024/9/19 12:02:32

### The error occurred while setting parameters
### SQL: SELECT dd.detail_id, dd.remind_time FROM dic_tab_map dtm LEFT JOIN dic_spot ds ON dtm.dic_id = ds.dic_id LEFT JOIN dic_detail dd ON dtm.dic_id = dd.dic_id WHERE ds.dept_id = ? AND dtm.table_name = ? AND dtm.column_bus_name = ?
### Cause: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying建议：No operator matches the given name and argument types. You might need to add explicit type casts.位置：173
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying建议：No operator matches the given name and argument types. You might need to add explicit type casts.位置：173at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)at com.sun.proxy.$Proxy138.selectList(Unknown Source)at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)at com.sun.proxy.$Proxy235.getDicByTableMap(Unknown Source)at com.ruoyi.data.service.DicJobService.getDicByTableMap(DicJobService.java:26)at com.ruoyi.data.job.DataGet.getZfbaData(DataGet.java:38)... 6 more
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = character varying建议：No operator matches the given name and argument types. You might need to add explicit type casts.位置：173at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)

解决：

使用cast( xx as xx)进行类型转换

selectdd.detail_id ,dd.remind_time
fromdic_tab_map dtm
left join dic_spot ds ondtm.dic_id = ds.dic_id
left join dic_detail dd ondtm.dic_id = dd.dic_id
whereds.dept_id = cast('130123' as bigint)