org.postgresql.util.PSQLException: ERROR: syntax e

问题:

I am getting this PSQLException:

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

When I run the following code:

ps = connection.prepareStatement("SELECT current_timestamp + INTERVAL ?;");
ps.setString(1, "30 minutes");
System.out.println(ps);
rs = ps.executeQuery();

However, the println function displays this in the console:

SELECT current_timestamp + INTERVAL '30 minutes'

Anyone know what is wrong? The query in the console runs fine in pgAdmin so I know it isn't a syntax error.

回答1:

Although the syntax INTERVAL '30 minutes' is valid when you write SQL directly in a console, it is actually considered to be an interval literal and won't work where the string that follows the word INTERVAL is not a literal string.

Prepared statements in PostgreSQL are implemented on the server side using PREPARE and each ? is seen as an actual variable on the server. This is also why it complains about $1 although you never wrote a $ in your statement.

Therefore, literal syntax does not work for a prepared statement.

Don't let the string representation (result of println) of the prepared statement confuse you - it's not what the server sees. The server sees a variable there.

Thus, you need to use syntax that takes a string (which can be a variable or a literal) and converts it to interval. For example ?::INTERVAL or CAST(? AS INTERVAL).

This is therefore not a bug.

回答2:

I believe this is a Postgres bug and so I thought of a dirty hack to get around this...

ps = connection.prepareStatement("SELECT current_timestamp + INTERVAL ?;");
ps.setString(1, "30 minutes");
ps = connection.prepareStatement(ps.toString());
rs = ps.executeQuery();

I wonder if this will ever get fixed?