I have a Java application connecting to a PostgreSQL database.
Using straightforward code like:
ps = con.prepareStatement(sql);
if (dataTypes != null && dataTypes.size() > 0) {
for (int i = 0; i < dataTypes.size(); ++i) {
if (dataTypes.get(i) == DataTypes.NUMERIC)
ps.setLong((i + 1), Long.parseLong(values.get(i)));
else
ps.setString((i + 1), values.get(i));
}
}
rs = ps.executeQuery();
and with a query like
select ? from dummy
it just works fine. In the postgres logs what shows is:
2015-06-13 12:48:30 EEST [28294-3] xx LOG: execute <unnamed>: select $1 from dummy
2015-06-13 12:48:30 EEST [28294-4] xx DETAIL: parameters: $1 = '0'
But with a query like:
select to_char(now()+interval ? day, 'YYYYMMDD') from dummy
I get a syntax error:
Cause [org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
and in the postgres logs:
2015-06-13 13:17:39 EEST [29311-3] xxx ERROR: syntax error at or near "$1" at character 31
2015-06-13 13:17:39 EEST [29311-4] xxx STATEMENT: select to_char(now()+interval $1 day, 'YYYYMMDD') from dummy
but in pgadmin something like
select to_char(now()+interval '0' day, 'YYYYMMDD') from dummy
works just fine without any error.
I just can't figure out what the problem is. Why does one of the queries work but the other one doesn't?
My PostgreSQL version is 9.4