Postgres: Error [42601] Error: Syntax error at or near "$2". Error while executing the Query

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

In the above query, In the where clause, (the first line: (messages. TIME > TIMESTAMP ? - '30 day'::INTERVAL) AND ) when parameter is typecasted (i.e when I put TIMESTAMP before ?) I get the following error

Which is not true for the text data in the second line (messages. TIME < TIMESTAMP '2016-08-03' + '1 day'::INTERVAL) AND

When I change the clause to ( messages. TIME > ? ::TIMESTAMP - '30 day'::INTERVAL) AND ) ( i.e I put the TIMESTAMP after ? ) Can anybody throw some light on what is happening? Thanks!!

Note: PostGresVersion - 9.6 OdBC driver - 32 bit driver (located at C:\Program Files (x86)\psqlODBC\0905\bin\psqlodbc30a.dll.) Same is true with Postgres 8.4 as well.

Answer 1

The documentation says:

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

type 'string'
'string'::type
CAST ( 'string' AS type )

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 type 'string' syntax can only be used to specify the type of a simple literal constant.

So you can use this syntax only with a string literal and not with a parameter as you are trying to do.