date_trunc org.postgresql.util。PSQLException:错误:在$1或附近的语法错误

I get this error while running this Java/JDBC code. Any ideas how to get around it?

在运行这个Java/JDBC代码时，我得到了这个错误。有什么办法解决这个问题吗?

Seems like it's complaining about the parameter in date_trunc function?

好像是在抱怨date_trunc函数中的参数?

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 100

org.postgresql.util。PSQLException:错误:在$1的位置或附近的语法错误:100。

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:2161)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1890)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:560)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:417)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:302)

Java code:

Java代码:

static PreparedStatement searchErrorPP = connection.prepareStatement(
"select count(*) from tracking where date_trunc('day', run_date) <= 
     date_trunc('day', timestamp ?)");



public static int queryCount(java.util.Date date) throws SQLException {


  PreparedStatement ps = null;
  try {
      ps = searchErrorPP;
      ps.setDate( 1, new java.sql.Date(date.getTime()));
      ResultSet rs = ps.executeQuery();

resulting query which executes fine in pgAdmin:

结果查询在pgAdmin中执行良好:

select count(*) from tracking where date_trunc('day', run_date) <= 
           date_trunc('day', timestamp '2014-11-11 -05:00:00')

2 个解决方案

#1

When using the type 'string' syntax as in timestamp '2014-11-11 -05:00:00', the value provided must be a constant, not a parameter. It's interpreted and converted to an internal timestamp representation by the SQL engine at the parse stage, before actual execution takes place and before the values for the parameters are known.

当使用类型'string'语法时，如时间戳'2014-11-11 -05:00:00'，所提供的值必须是一个常量，而不是一个参数。它被解释并转换为SQL引擎在解析阶段的内部时间戳表示，在实际执行发生之前和参数的值已知之前。

So when encountering timestamp $1, the parser produces a syntax error because $1 is not a literal string.

因此，当遇到timestamp $1时，解析器会产生一个语法错误，因为$1不是一个字串。

On the other hand, the value of cast($1 as timestamp) will be produced at execute stage, so this is what should be used.

另一方面，cast($1作为时间戳)的值将在执行阶段生成，所以这是应该使用的。

As for the syntax from the point of view of JDBC, cast(? as timestamp) should be fine. The PostgreSQL specific syntax ?::timestamp with double colons also probably works.

至于从JDBC的角度来看语法，cast(?作为时间戳)应该没问题。PostgreSQL特定的语法?::时间戳和双冒号也可能有效。

#2

I have seen such an error when either of the jdbc connection url components are null.

当jdbc连接url组件都为空时，我看到了这样的错误。

E.g. connection url components such as DB host name, dbname, user or password are null.

例如，连接url组件(如DB主机名、dbname、用户或密码)为空。

Please check out there and you should find a clue.

请检查一下，你应该找到线索。

#1