Postgresql 15 - syntax sensitivity

Question

I've noticed that in older version of PG (example 13) when I had query like:

select 1 where 1=1and 2=2

all was OK

but i try this in PG 15 I get error: trailing junk after numeric literal at or near "1a"

Have something changed or maybe there is a new option in configuration to make it more strict ?

To be honest, I am surprised that Postgres accepted that at all. — user330315
– user330315, Commented Jan 30, 2023 at 9:23
Weird !! Also select 1 where 1=1or 2=2 is working on PG 13 and 14 — SelVazi
– SelVazi, Commented Jan 30, 2023 at 9:57

snakecharmerb · Accepted Answer · 2023-01-30 10:41:51Z

12

This was changed in v 15.0.

Prevent numeric literals from having non-numeric trailing characters (Peter Eisentraut)

Previously, query text like 123abc would be interpreted as 123 followed by a separate token abc.

and similar

Adjust JSON numeric literal processing to match the SQL/JSON-standard (Peter Eisentraut)

This accepts numeric formats like .1 and 1., and disallows trailing junk after numeric literals, like 1.type().

answered Jan 30, 2023 at 10:41

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Huisum · Accepted Answer · 2023-04-26 06:49:09Z

0

For those looking for a fix in their query:

Make sure you signal that your query is a string. E.g:

SELECT * FROM db WHERE col IN (12A2, 1E23)

does not work but

SELECT * FROM db WHERE col IN ('12A2', '1E23')

does the job.

The problem above is solved by placing a space like so:

SELECT 1 WHERE 1=1 AND 2=2

answered Apr 26, 2023 at 6:49

Huisum

1