org.postgresql.util.PSQLException：错误：“$1”处或附近的语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33058684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
提问by Kevin Orriss
I am getting this PSQLException:
我收到这个 PSQLException：
org.postgresql.util.PSQLException: ERROR: syntax error at or near ""
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:
但是， println 函数在控制台中显示：
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.
有谁知道出了什么问题？控制台中的查询在 pgAdmin 中运行良好，所以我知道这不是语法错误。
回答by RealSkeptic
Although the syntax INTERVAL '30 minutes'is valid when you write SQL directly in a console, it is actually considered to be an interval literaland won't work where the string that follows the word INTERVALis not a literal string.
尽管INTERVAL '30 minutes'直接在控制台中编写 SQL 时语法是有效的，但它实际上被认为是间隔文字，并且在单词INTERVAL后面的字符串不是文字字符串时不起作用。
Prepared statements in PostgreSQL are implemented on the server side using PREPAREand each ?is seen as an actual variable on the server. This is also why it complains about $1although you never wrote a $in your statement.
PostgreSQL 中的预处理语句在服务器端使用实现PREPARE，每个语句都?被视为服务器上的实际变量。这也是它抱怨的原因，$1尽管您从未$在声明中写过 a 。
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.
不要让println准备好的语句的字符串表示（结果）使您感到困惑 - 这不是服务器看到的。服务器在那里看到一个变量。
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 ?::INTERVALor CAST(? AS INTERVAL).
因此，您需要使用接受字符串（可以是变量或文字）并将其转换为区间的语法。例如?::INTERVAL或CAST(? AS INTERVAL)。
This is therefore nota bug.
因此，这不是错误。
回答by Kevin Orriss
I believe this is a Postgres bug and so I thought of a dirty hack to get around this...
我相信这是一个 Postgres 错误，所以我想到了一个肮脏的黑客来解决这个问题......
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?
我想知道这是否会得到修复？
