org.postgresql.util.PSQLException:错误:语法错误在"$ 1"处或附近. [英] org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

问题描述

我收到此PSQLException:

I am getting this PSQLException:

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

当我运行以下代码时:

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

但是，println函数会在控制台中显示此内容:

However, the println function displays this in the console:

SELECT current_timestamp + INTERVAL '30 minutes'

有人知道怎么了吗?控制台中的查询在pgAdmin中运行良好，因此我知道这不是语法错误.

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

推荐答案

尽管直接在控制台中编写SQL时语法INTERVAL '30 minutes'是有效的，但实际上它被认为是间隔文字并且在INTERVAL后面的字符串不是文字字符串的情况下将不起作用.

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.

使用 PREPARE 和每个?都被视为服务器上的实际变量.这也是为什么它抱怨$1的原因，尽管您从未在语句中写过$.

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.

不要让准备好的语句的字符串表示形式(println的结果)使您感到困惑-这不是服务器看到的.服务器在那里看到一个变量.

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.

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

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

因此，这不是错误.

这篇关于org.postgresql.util.PSQLException:错误:语法错误在"$ 1"处或附近.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！