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

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 issue I was encountering. I really appreciate your time and support.

Best regards,
Wang Bo

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

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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