Postgresql – Syntax error using preparedstatements with postgresql

javajdbcpostgresqlprepared-statement

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

Best Answer

The common solution is to express this as:

interval '1 day' * ?

with ? as a placeholder for a numeric value (possibly with a fractional part).

The syntax tried in the question is rejected because for the SQL grammar, the entire expression interval '1 day' is a constant. It cannot be changed by injecting a placeholder into it, just like we couldn't write 3.? and pass .1415926 as an external parameter to form the PI number.

Related Question