postgresql - Postgres : Error [42601] Error: Syntax error at or near "$2". 执行查询时出错

Question

SELECT     
    to_char(messages. TIME, 'YYYY/MM/DD') AS FullDate, 
    to_char(messages. TIME, 'MM/DD') AS PartialDate, 
             COUNT(CASE WHEN message_definitions.error_category = ? THEN 1 END) AS Errors, 
    error_categories.threshold, 
    COUNT(CASE WHEN messages.message_id = 14 THEN 1 END) AS Picks
FROM    
    messages LEFT JOIN
    message_definitions USING (message_id) LEFT JOIN
    error_categories USING (error_category)
WHERE   
    (messages. TIME > TIMESTAMP ? - '30 day'::INTERVAL) AND 
    (messages. TIME < TIMESTAMP '2016-08-03' + '1 day'::INTERVAL) AND 
    (messages.system_id = ?) AND 
    (messages.message_id = 14 OR
     message_definitions.error_category = ?)
GROUP BY 
    FullDate, PartialDate, error_categories.threshold
ORDER BY
    FullDate DESC LIMIT 40

在上面的查询中，在 where 子句中，(第一行:(messages.TIME > TIMESTAMP ?- '30 day'::INTERVAL) AND) 当参数被类型化时(即当我把 TIMESTAMP 放在前面时？)我得到以下错误

第二行中的文本数据不正确(messages.TIME <TIMESTAMP '2016-08-03' + '1 day'::INTERVAL)AND

当我将子句更改为 ( messages.TIME > ?::TIMESTAMP - '30 day'::INTERVAL) AND)(即我把 TIMESTAMP 放在后面？)任何人都可以阐明正在发生的事情吗？谢谢!!

注意:PostGresVersion - 9.6OdBC 驱动程序 - 32 位驱动程序(位于 C:\Program Files (x86)\psqlODBC\0905\bin\psqlodbc30a.dll。)Postgres 8.4 也是如此。

Answer 1

The documentation说:

A constant of an arbitrary type can be entered using any one of the following notations:

<em>type</em> '<em>string</em>'
'<em>string</em>'::<em>type</em>
CAST ( '<em>string</em>' AS <em>type</em> )

The string constant's text is passed to the input conversion routine for the type called type. The result is a constant of the indicated type.

[...]

The ::, CAST(), and function-call syntaxes can also be used to specify run-time type conversions of arbitrary expressions, as discussed in Section 4.2.9. To avoid syntactic ambiguity, the <em>type</em> '<em>string</em>' syntax can only be used to specify the type of a simple literal constant.

因此，您只能将此语法与字符串文字一起使用，而不能像您尝试的那样与参数一起使用。