Spring Repository执行PostgreSQL原生SQL时::interval语法报错如何解决

错误原因 #

Spring Data JPA 的 @Query 注解默认会将单冒号 : 识别为SQL命名参数的前缀，你所用的PostgreSQL特有的双冒号类型转换语法 ::interval 会被Spring的SQL解析器误识别为参数占位符，最终触发语法错误。

解决方案 #

推荐优先使用兼容性更高的标准SQL写法，无需处理转义：

方案1：使用CAST函数做类型转换 #

将 '1 hour'::interval 替换为标准SQL的CAST写法，修改后的完整代码如下：

@Repository
public interface OrderRepository extends CrudRepository<Orders, Long> {
    @Modifying
    @Transactional
    @Query(value = "DELETE FROM orders WHERE created_at < now() - CAST('1 hour' AS INTERVAL)",
        nativeQuery = true)
    void executeDelete();
}

方案2：直接使用INTERVAL关键字定义时间间隔 #

PostgreSQL支持直接用 INTERVAL '时间长度' 的语法定义时间间隔，写法更简洁：

@Repository
public interface OrderRepository extends CrudRepository<Orders, Long> {
    @Modifying
    @Transactional
    @Query(value = "DELETE FROM orders WHERE created_at < now() - INTERVAL '1 hour'",
        nativeQuery = true)
    void executeDelete();
}

方案3：转义双冒号（不推荐） #

如果需要保留双冒号写法，可对冒号进行转义，不同版本Spring Data JPA转义规则略有不同：

• 低版本使用双反斜杠转义：

@Query(value = "DELETE FROM orders WHERE created_at < now() - '1 hour'\\:\\:interval", nativeQuery = true)

• 2.4及以上版本可使用三个连续冒号转义：

@Query(value = "DELETE FROM orders WHERE created_at < now() - '1 hour':::interval", nativeQuery = true)

内容的提问来源于stack exchange，提问作者user1285928