postgresql 错误：操作员不存在：日期=字符变化

ikfrs5lh 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(658)

我有这个问题：
PostgreSQL PG Admin中的此查询工作正常：

SELECT ST_AsGeoJSON(t1.\*) as extent FROM ricepest.forecast t1 where state = 'Andhra Pradesh' AND dist = 'Ananthapur' AND date_id = '2023-01-01';

但是在spring-boot中，我用hibernate写了一个这样的服务：

@Query(nativeQuery = true, value = "SELECT ST_AsGeoJSON(t1.*) as extent FROM ricepest.state t1 where state = :val")
    List<GetFeature> getFeatureByStateIdService(@Param("val") String val);
    
    @Query(nativeQuery = true, value = "SELECT ST_AsGeoJSON(t1.*) as extent FROM ricepest.district t1 where state = :state_val AND dist = :dist_val")
    List<GetFeature> getFeatureByStateIdService(@Param("state_val") String state_val, @Param("dist_val") String dist_val);
    
    @Query(nativeQuery = true, value = "SELECT ST_AsGeoJSON(t1.*) as extent FROM ricepest.forecast t1 where state = :state_val AND dist = :dist_val AND date_id = :from_date")
    List<GetFeature> getFeatureByStateIdService(@Param("state_val") String state_val, @Param("dist_val") String dist_val, @Param("from_date") String from_date);
    
    public static interface GetFeature {
        String getExtent();
    }

这是我的java控制器：

@PostMapping(value = "/feature")
    public ResponseEntity<List<GetFeature>> getFeatureByStateIdService(@RequestBody RequestInputs reqInputs) {
        System.out.println("The state and district input value for getFeature : " + reqInputs.getState_id() + " and " + reqInputs.getDist() + " and " + reqInputs.getFromdate_id());
        List<GetFeature> extent;
        if (reqInputs.getDist().equalsIgnoreCase("0") || reqInputs.getDist().equalsIgnoreCase("-- SELECT DISTRICT--")) {
            extent = pestServiceRepository.getFeatureByStateIdService(reqInputs.getState_id());
        } else if (reqInputs.getFromdate_id().equalsIgnoreCase("0") || reqInputs.getFromdate_id().equalsIgnoreCase("-- SELECT DATE--")) {
            extent = pestServiceRepository.getFeatureByStateIdService(reqInputs.getDist());
        } else {
            extent = pestServiceRepository.getFeatureByStateIdService(reqInputs.getState_id(), reqInputs.getDist(), reqInputs.getFromdate_id());
        }
        if (!extent.isEmpty()) {
            return new ResponseEntity<>(extent, HttpStatus.OK);
        } else {
            return new ResponseEntity<>(HttpStatus.NOT_FOUND);
        }
    }

我有这个错误：

ERROR: operator does not exist: date = character varying
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

我不明白哪里出了问题。任何帮助将不胜感激。

postgresql

来源：https://stackoverflow.com/questions/76412167/error-operator-does-not-exist-date-character-varying

1条答案

按热度按时间

gpnt7bae1#

在pgAdmin中，由于表达式的上下文，文字'2023-01-01'被解析为日期值，而不是文本值。在使用Sping Boot 和Hibernate的Java代码中，该参数已显式指定为String类。PostgreSQL在比较之前不执行文本值到日期值的隐式转换。解析SQL语句和使用显式参数计算语句之间存在根本区别。将参数作为日期传递，或在查询中将文本值显式转换为日期。将参数作为预期类型传递是首选方法。
以下语句可以在pgAdmin中运行，它们演示了不同之处：

-- because '2023-06-06' is parsed as a date, this query will not raise an exception
SELECT current_date = '2023-06-06';

-- because test_date is explicitely made a date, this query will not raise an exception
-- this is analogous to passing a date value to a parameterized query
WITH t(test_date) AS (
  VALUES ('2023-06-06'::date)
)
SELECT current_date = t.test_date
FROM t;

-- this query will raise an exception due to type differences
-- there is no context to cause PostgreSQL to parse '2023-06-06' as a date
WITH t(test_date) AS (
  VALUES ('2023-06-06')
)
SELECT current_date = t.test_date
FROM t;

-- because of the explicit type cast, this query will not raise an exception
-- this is similar to passing a string and explicitly casting it to date in the SELECT
WITH t(test_date) AS (
  VALUES ('2023-06-06')
)
SELECT current_date = t.test_date::date
FROM t;

赞(0）回复(0）举报 2023-06-22

我来回答

postgresql 错误：操作员不存在：日期=字符变化

1条答案

相关问题

热门标签

最新问答