上下文正在从休息服务器连接到 Postgres 数据库。
考虑一个假设的代表性示例:我希望能够获得一个名称列表,其中帐户创建日期比任意值早/新。
在下面的示例查询中,表结构很简单 -name
是 type text
,并且creation_date
是 type timestamp
。所以当我做类似的事情时
server_pg_module:query("select name from new_table where
current_timestamp - creation_date < '6 days'")
它工作得很好。但我真正想做的是6 days
从服务器获取该值。所以我尝试类似
server_pg_module:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]
它抛出一个错误。我试过了'6 days'
,"'6 days'"
还有其他一些混合物,都抛出错误。所以要检查我添加了一个新interval
的类型列interval
并尝试了一个类似的查询
server_pg_module:query("insert into new_table (name, interval) values ($1, '3 day')", ["fooo"]).
哪个有效,但是
server_pg_module:query("insert into new_table (name, interval) values ($1, $2)", ["fooo", "3 days"]).
休息。为了更好地衡量,除了"'3 days'"
上面提到的混合物之外,我还尝试$2::interval
了(我不确定这是否合法),但它不起作用。
因此,我相信它可能与在参数查询中表达间隔有关,或者与我正在使用的模块有关。任何关于导致麻烦的原因以及如何做这类事情的想法都将不胜感激。或者可以缩小范围,问题不在于 pg 而在于模块,然后我必须在其他地方解决它。
Postgres 版本:10.x
我正在使用的模块是 pgo(用于 Erlang 编程语言)https://github.com/SpaceTime-IoT/pgo。我得到的错误消息(当我传递"2 days"
或"'2 days'"
作为查询参数时)看起来像:
{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
line => <<"575">>,
message => <<"insufficient data left in message">>,
routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
{unknown,86} => <<"ERROR">>}}}
当我'2 days'
作为参数传递时,它会引发badarg
错误。
TLDR:跳到下面的“高级查询”一章。
您没有透露您正在使用的模块,但问题显然是类型转换之一。看起来您的参数是作为类型值传递的,我假设
text
orvarchar
。text
->没有隐式类型转换interval
:db<>在这里摆弄
如果我的假设是正确的,您应该会看到如下错误消息:
我非常有信心您未公开的模块可以传递不同的数据类型或无类型的字符串文字。Postgres 确实提供了这个功能。
你还说:
这很奇怪,因为显式类型转换也应该起作用。
演示
第 2 行: ... current_timestamp - 时间戳 '2018-05-04 18:40' < text '6 ... ^ 提示:没有运算符与给定的名称和参数类型匹配。您可能需要添加显式类型转换。
准备好的语句也是如此:
第 3 行:...选择 current_timestamp - 时间戳 '2018-05-04 18:40' < $1; ^ 提示:没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。
db<>在这里摆弄
高级查询
除了所有这些,您的查询都不能使用索引(不是“sargable”)。 改用这样的东西!
您可以乘以
interval
withinteger
。或者,如果您发现传递类型参数的问题:
用来
localtimestamp
指出“当前时间”取决于您当前的时区设置,类型为timestamp
. 细节:我想出了一个解决方法来实现相同的目标。
该方法利用了两个日期的差是整数这一事实。将时间戳转换为日期很简单,整数值可用于从服务器到数据库的参数化查询。