Thread: Request for official clarification on SQL parameter parsing changes in PostgreSQL 15 and 16

On Thu, Apr 17, 2025 at 11:13 AM 王　博 <bo.wang@infortech.co.jp> wrote:
> Hello PostgreSQL Community,
> We have encountered unexpected SQL parsing errors during application upgrades from PostgreSQL 14 to 15 and 16,
relatedto the use of JDBC-style parameter placeholders (`?`) in our legacy applications. 

AFAIK, PostgreSQL itself only supports $1, $2, ... bind placeholders.
Thus I suspect it's your JDBC driver that adds support for ? style placeholders.
So look in the release notes for that, instead of PostgreSQL itself. --DD

On Thu, 2025-04-17 at 05:17 +0000, 王　博 wrote:
> We have encountered unexpected SQL parsing errors during application upgrades from
> PostgreSQL 14 to 15 and 16, related to the use of JDBC-style parameter
> placeholders (`?`) in our legacy applications.
>  
> Specifically:
>  
> 1. In PostgreSQL 15 and later:
>    The following SQL causes a syntax error unless a space is added after the `?`:
>      SELECT * FROM table WHERE a = ?AND b = 123;
>    → Adding a space (`? AND`) resolves the issue.
>    This did not cause an error in PostgreSQL 14.
>  
> 2. In PostgreSQL 16:
>    The following SQL causes a syntax error:
>      SELECT * FROM table WHERE a = ?ORDER BY b;
>    → This query runs in PostgreSQL 15, but causes an error in 16 unless a space is added after `?`.
>  
> We understand that this behavior may stem from changes to the parser to make it more
> strict and SQL-compliant. However, we were unable to find any explicit mention of these
> changes in the official release notes of PostgreSQL 15 or 16.
>  
> Our client is asking for official documentation or a statement confirming whether this
> behavior change was intentional and when it was introduced.
>  
> Could anyone kindly point us to:
> - Relevant release note sections, commits, or discussions on this change
> - Confirmation that these are intentional parser changes (and not regressions)

I'd say it is this change:

https://postgr.es/c/2549f0661bd28571d7200d6f82f752a7ee5d47e1

The mailing list discussion is here:

https://www.postgresql.org/message-id/flat/b239564c-cad0-b23e-c57e-166d883cb97d@enterprisedb.com

Yours,
Laurenz Albe

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Thu, 2025-04-17 at 05:17 +0000, 王　博 wrote:
>> 1. In PostgreSQL 15 and later:
>>    The following SQL causes a syntax error unless a space is added after the `?`:
>>      SELECT * FROM table WHERE a = ?AND b = 123;
>>    → Adding a space (`? AND`) resolves the issue.

> I'd say it is this change:
> https://postgr.es/c/2549f0661bd28571d7200d6f82f752a7ee5d47e1

Yeah.  This looks like "?" ought to be parsable as a separate token
... but as Dominique noted, it's not actually legal syntax in any
version of Postgres.  Something in your client stack must be
translating "?" to "$1", "$2", etc, and so the new prohibition
against junk trailing a number applies.

You could fix this without application-level changes if you fixed
whatever is making that substitution to add spaces around the
parameter symbol.  It's really a bug that it didn't do so already,
since closely-adjacent cases like digits immediately after the
"?" would already have caused failures.

            regards, tom lane

Dear Tom, Laurenz, and Dominique,

Thank you all very much for your helpful and detailed explanations.

Your insights clarified the behavior change in PostgreSQL 15 perfectly, and I now have a clear understanding of the
issueI was encountering. I really appreciate your time and support.
 

Best regards,  
Wang Bo


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Thursday, April 17, 2025 11:16 PM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: 王　博 <bo.wang@infortech.co.jp>; pgsql-general@lists.postgresql.org; 李　浩 <hao.li@infortech.co.jp>
Subject: Re: Request for official clarification on SQL parameter parsing changes in PostgreSQL 15 and 16

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Thu, 2025-04-17 at 05:17 +0000, 王　博 wrote:
>> 1. In PostgreSQL 15 and later:
>>    The following SQL causes a syntax error unless a space is added after the `?`:
>>      SELECT * FROM table WHERE a = ?AND b = 123;
>>    → Adding a space (`? AND`) resolves the issue.

> I'd say it is this change:
> https://postgr.es/c/2549f0661bd28571d7200d6f82f752a7ee5d47e1

Yeah.  This looks like "?" ought to be parsable as a separate token ... but as Dominique noted, it's not actually legal
syntaxin any version of Postgres.  Something in your client stack must be translating "?" to "$1", "$2", etc, and so
thenew prohibition against junk trailing a number applies.
 

You could fix this without application-level changes if you fixed whatever is making that substitution to add spaces
aroundthe parameter symbol.  It's really a bug that it didn't do so already, since closely-adjacent cases like digits
immediatelyafter the "?" would already have caused failures.
 

            regards, tom lane