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

Question

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

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

org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1" Position: 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:

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:

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

Answer 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.

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

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

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.