org.postgresql.util。PSQLException:错误:在$1或附近的语法错误

I am getting this PSQLException:

我得到了这个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:

但是，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中运行良好，因此我知道它不是语法错误。

2 个解决方案

#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.

尽管在一个控制台中直接编写SQL时，语法间隔“30分钟”是有效的，但实际上它被认为是一个间隔文字，并且不会工作，因为在这个字串后面的字符串不是一个字串。

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.

在PostgreSQL中编写的语句在服务器端使用准备和每一个实现吗?被看作是服务器上的一个实际变量。这也是为什么它会抱怨1美元，尽管你在声明中从未写过一美元。

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 ?::INTERVAL or CAST(? AS INTERVAL).

因此，您需要使用带有字符串(可以是变量或文字)的语法，并将其转换为间隔。例如::INTERVAL or CAST(?作为间隔)。

This is therefore not a bug.

因此，这不是一个错误。

#2

-1

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

我相信这是一个Postgres的bug，所以我想到了一个肮脏的黑客来绕过这个…

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?

我想知道这是否会得到修复?

#1