JDBI/Postgress Select查询失败，日期时间比较

Question

后继版本：10.4

表列名/类型：带时区的updated_at timestamp(6)

final String SELECT_PAYMENT_INFO_QUERY =
    "SELECT i.code, p.* 
    FROM information i 
    INNER JOIN product p ON i.ref = p.information_ref 
    WHERE p.reference = :reference AND p.type = :paymentMethodType AND p.error_state = :errorState 
    AND p.updated_at IS NOT NULL AND p.updated_at >= :queryFromTime 
    ORDER BY p.updated_at ASC 
    FETCH FIRST :numberOfRows ROWS ONLY"

Java方法：

    public List<ProductInformationDTO> retrieveProductInformations(
      ZonedDateTime fetchFromTime, int noOfRecords)
  {
    try
    {
      return dbi.inTransaction((HandleCallback<List<ProductInformationDTO>, Exception>) handle ->
      {
        final List<ProductInformationDTO> productInfos = handle.createQuery(SELECT_PAYMENT_INFO_QUERY)
            .bind("reference", "ABCD")
            .bind("paymentMethodType", "CREDIT_CARD")
            .bind("errorState", "COMPLETED_WITH_ERRORS")
            .bind("queryFromTime", fetchFromTime.toOffsetDateTime())
            .bind("numberOfRows", noOfRecords)
            .map(productInfoMapper)
            .list();

        return productInfos;
      });
    }
    catch (Exception e)
    {
      LOG.error("Failed to complete the retrieve operation.", e);
      throw new TransactionException(e);
    }
  }

这个查询和方法在H2数据库中很好地工作。然而，当使用Postgress数据库对其进行测试时，我得到以下异常

我也尝试了FetchFromTime.ToLocalDateTime()，但得到了相同的异常。

Answer 1

不能在fetch first（或limit)子句中使用位置参数。假设您能够容忍运行本机查询，一种解决方法是使用row_number:

SELECT *
FROM
(
    SELECT i.code, p.*, ROW_NUMBER() OVER (ORDER BY p.updated_at) rn
    FROM information i 
    INNER JOIN product p ON i.ref = p.information_ref 
    WHERE p.reference = :reference AND p.type = :paymentMethodType AND
          p.error_state = :errorState AND p.updated_at IS NOT NULL AND
          p.updated_at >= :queryFromTime 
) t
WHERE rn <= :numberOfRows