Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
You should be able to use string concatenation too, something like NOW() + (? || ' days')::interval. –  Bruno Jun 13 at 15:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.