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
15
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
15
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?
我想知道这是否会得到修复?