public inbox for [email protected]help / color / mirror / Atom feed
SQL statements error in pgsql 16 but not in pgsql 12 7+ messages / 4 participants [nested] [flat]
* SQL statements error in pgsql 16 but not in pgsql 12 @ 2024-07-15 22:57 Frank Komsic <[email protected]> 0 siblings, 2 replies; 7+ messages in thread From: Frank Komsic @ 2024-07-15 22:57 UTC (permalink / raw) To: [email protected] <[email protected]> Hi, I was trying to upgrade our database from version 12.18 to the latest 16.3 After upgrading, the application has reported errors or “junk info” in the sql command line. Example SQL code: Message: ERROR: 42601: trailing junk after numeric literal at or near "19749O" SQL: SELECT bb,mass,classifier,lot FROM lab.pt_f003_view WHERE (lot = 19749OR lot= 19750) ORDER BY lot,bb The above sql command works in version 12 but does not work in version 16 due to missing space in “WHERE (lot = 19749OR lot= 19750)” Why did this work before and now it does not work? Thanks Frank Komsic ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL statements error in pgsql 16 but not in pgsql 12 @ 2024-07-15 23:23 David G. Johnston <[email protected]> parent: Frank Komsic <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: David G. Johnston @ 2024-07-15 23:23 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] <[email protected]> On Mon, Jul 15, 2024 at 3:57 PM Frank Komsic <[email protected]> wrote: > The above sql command works in version 12 but does not work in version 16 > due to missing space in “WHERE (lot = 19749OR lot= 19750)” > > > > Why did this work before and now it does not work? > > > It seems to be a side-effect of supporting non-decimal literals. https://www.postgresql.org/message-id/flat/[email protected] Git: 6fcda9aba83449082124825b6d375c0a61e21c42 This comment in particular refers to a v15 change that is probably related if you want to dive deeper. https://www.postgresql.org/message-id/flat/1467947.1669851407%40sss.pgh.pa.us#66391c571b4db866e22245... Suffice to say, we became more strict here since v12 and you will need to modify the query accordingly. David J. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL statements error in pgsql 16 but not in pgsql 12 @ 2024-07-16 14:56 Bharani SV-forum <[email protected]> parent: Frank Komsic <[email protected]> 1 sibling, 1 reply; 7+ messages in thread From: Bharani SV-forum @ 2024-07-16 14:56 UTC (permalink / raw) To: [email protected] <[email protected]>; Frank Komsic <[email protected]> cross checkWHERE (lot = 19749 OR lot= 19750) where the OR is tagged to numeric 19749. On Monday, July 15, 2024 at 06:58:06 PM EDT, Frank Komsic <[email protected]> wrote: Hi, I was trying to upgrade our database from version 12.18 to the latest 16.3 After upgrading, the application has reported errors or “junk info” in the sql command line. Example SQL code: Message: ERROR: 42601: trailing junk after numeric literal at or near "19749O" SQL: SELECT bb,mass,classifier,lot FROM lab.pt_f003_view WHERE (lot = 19749OR lot= 19750) ORDER BY lot,bb The above sql command works in version 12 but does not work in version 16 due to missing space in “WHERE (lot = 19749OR lot= 19750)” Why did this work before and now it does not work? Thanks Frank Komsic ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL statements error in pgsql 16 but not in pgsql 12 @ 2024-07-16 15:00 David G. Johnston <[email protected]> parent: Bharani SV-forum <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: David G. Johnston @ 2024-07-16 15:00 UTC (permalink / raw) To: Bharani SV-forum <[email protected]>; +Cc: [email protected] <[email protected]>; Frank Komsic <[email protected]> On Tue, Jul 16, 2024 at 7:58 AM Bharani SV-forum <[email protected]> wrote: > cross check > WHERE (lot = 19749 *OR* lot= 19750) > > where the OR is tagged to numeric 19749. > > What does "tagged" mean here? David J. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL statements error in pgsql 16 but not in pgsql 12 @ 2024-07-16 18:01 Frank Komsic <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Frank Komsic @ 2024-07-16 18:01 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]> Thank you for your reply. I gather I will have to lookover the entire code and set the SQL statements correctly. Thanks Frank From: David G. Johnston <[email protected]> Date: Monday, July 15, 2024 at 7:24 PM To: Frank Komsic <[email protected]> Cc: [email protected] <[email protected]> Subject: Re: SQL statements error in pgsql 16 but not in pgsql 12 On Mon, Jul 15, 2024 at 3:57 PM Frank Komsic <[email protected]<mailto:[email protected]>> wrote: The above sql command works in version 12 but does not work in version 16 due to missing space in “WHERE (lot = 19749OR lot= 19750)” Why did this work before and now it does not work? It seems to be a side-effect of supporting non-decimal literals. https://www.postgresql.org/message-id/flat/[email protected] Git: 6fcda9aba83449082124825b6d375c0a61e21c42 This comment in particular refers to a v15 change that is probably related if you want to dive deeper. https://www.postgresql.org/message-id/flat/1467947.1669851407%40sss.pgh.pa.us#66391c571b4db866e22245... Suffice to say, we became more strict here since v12 and you will need to modify the query accordingly. David J. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL statements error in pgsql 16 but not in pgsql 12 @ 2024-07-18 14:50 Bharani SV-forum <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 7+ messages in thread From: Bharani SV-forum @ 2024-07-18 14:50 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]>; Frank Komsic <[email protected]> tagged mean " add to something " hope ur picture posted with the sql query, i can see asWHERE (lot = 19749OR lot= 19750)instead of WHERE (lot = 19749 OR lot= 19750) Hope u have understood On Tuesday, July 16, 2024 at 11:00:54 AM EDT, David G. Johnston <[email protected]> wrote: On Tue, Jul 16, 2024 at 7:58 AM Bharani SV-forum <[email protected]> wrote: cross checkWHERE (lot = 19749 OR lot= 19750) where the OR is tagged to numeric 19749. What does "tagged" mean here? David J. ^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SQL statements error in pgsql 16 but not in pgsql 12 @ 2024-07-18 14:57 Tom Lane <[email protected]> parent: Bharani SV-forum <[email protected]> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2024-07-18 14:57 UTC (permalink / raw) To: Bharani SV-forum <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]>; Frank Komsic <[email protected]> Bharani SV-forum <[email protected]> writes: > tagged mean " add to something " > hope ur picture posted with the sql query, i can see asWHERE (lot = 19749OR lot= 19750)instead of > WHERE (lot = 19749 OR lot= 19750) That's not a great choice of word. In any case, the point here is that the SQL standard requires some whitespace between a number and a following word, and starting from PG 15 we're enforcing that to reduce ambiguity. regards, tom lane ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-07-18 14:57 UTC | newest] Thread overview: 7+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-07-15 22:57 SQL statements error in pgsql 16 but not in pgsql 12 Frank Komsic <[email protected]> 2024-07-15 23:23 ` David G. Johnston <[email protected]> 2024-07-16 18:01 ` Frank Komsic <[email protected]> 2024-07-16 14:56 ` Bharani SV-forum <[email protected]> 2024-07-16 15:00 ` David G. Johnston <[email protected]> 2024-07-18 14:50 ` Bharani SV-forum <[email protected]> 2024-07-18 14:57 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox