PostgreSQL查询在SQL编辑器正常，Spring项目运行报语法错误如何解决

问题根因 #

该错误是Hibernate解析原生SQL时与PostgreSQL的类型转换语法::冲突导致的：

• PostgreSQL中::是类型转换的语法糖，直接在数据库SQL编辑器中执行时可以被正常识别
• 但Hibernate处理原生SQL语句时，会把所有:开头的字符串识别为命名参数占位符，你SQL里的::interval、::date会被Hibernate误解析为需要绑定的参数，最终生成的SQL语法错误，因此抛出冒号位置附近的语法异常。

解决方案 #

推荐使用标准SQL的CAST函数替换PostgreSQL特有的::类型转换写法，完全避免语法冲突，修改后的SQL片段如下：

String sql = "select TRIM(e.nomemploye) || CASE WHEN e.nomemploye IS NULL OR TRIM(e.nomemploye) = '' THEN '' ELSE ' ' END || e.prenomemploye as user, " + 
        "to_char(h.connexion, 'HH24:MI:SS') as connexion, " + 
        "to_char(h.deconnexion, 'HH24:MI:SS') as deconnexion, " + 
        "to_char(CAST((h.duree_seconde || ' second') AS interval), 'HH24:MI:SS') as duree, " + 
        "to_char(CAST((SUM(h.duree_seconde) OVER (PARTITION BY CAST(h.connexion AS date)) || ' second') AS interval), 'HH24:MI:SS') duree_totale_par_date " + 
        "from employe e join utilisateur u on e.idemploye = u.idemploye " +
        "join histodureeconnexion h on h.iduser = u.idutilisateur " +
        "where 1=1 ";

如果需要保留::写法，也可以对冒号进行转义，把所有::替换成\\:\\:即可，不过兼容性不如CAST函数好。

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