postgresql数据库查询时动态拼接条件报character varying = xxxx错误的解决办法

正常可以用的，其实就是多了个类型转换，虽然不一定会执行到那里，但是会提前检查sql，所以需要显式指明类型**

case when coalesce (:#{#searchDTO.satellite}, '') != '' then satellite = cast(:#{#searchDTO.satellite} as varchar ) else 1=1 end

**异常不可用的

case when coalesce (:#{#searchDTO.satellite}, '') != '' then satellite = :#{#searchDTO.satellite} else 1=1 end

具体原因是因为sql执行时会先检查sql，不管条件满不满足都会校验条件内sql的正确性，所以对于未知类型的数据需要显式转换为pgsql需要的数据类型。

@Query查询时类传参样例

@Query(nativeQuery = true, value = "select substring(filedate, 0, 9) dataTime, count(*) count from db_data_abroad_satellite_info " +
            " where 1=1 " +
            " and case when coalesce (:#{#searchDTO.satellite}, '') != '' then satellite = cast(:#{#searchDTO.satellite} as varchar ) else 1=1 end " +
            " and case when coalesce (:#{#searchDTO.sensor}, '') != '' then sensor = cast(:#{#searchDTO.sensor} as varchar ) else 1=1 end " +
            " and case when coalesce (:#{#searchDTO.dataLevel}, '') != '' then data_level = cast(:#{#searchDTO.dataLevel} as varchar ) else 1=1 end " +
            " and case when coalesce (:#{#searchDTO.startTimeSearch}, '') != '' then start_time >= TO_TIMESTAMP(cast(:#{#searchDTO.startTimeSearch} as varchar ), 'YYYY-MM-DD HH24:MI:SS') else 1=1 end " +
            " and case when coalesce (:#{#searchDTO.endTimeSearch}, '') != '' then start_time <= TO_TIMESTAMP(cast(:#{#searchDTO.startTimeSearch} as varchar ), 'YYYY-MM-DD HH24:MI:SS') else 1=1 end " +
            " GROUP BY substring(filedate, 0, 9) order by dataTime asc "
    )
    List<Map<Object, Object>> selectGroupBySatelliteCount(DbDataAbroadSatelliteInfoSearchDTO searchDTO);