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.

Where is the code which binds parameters to the above query? Are you trying to run the above query verbatim? — Tim Biegeleisen

Laurenz Albe Laurenz Albe · Accepted Answer · 2016-11-16T08:45:25

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.

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

2 Answers