date_trunc org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
java, jdbc, postgresql
Solution
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.
Problem
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') ```