Re: BUG #19652: to_number() silently truncates over-length integers

чт, 3 сент. 2026 г. в 18:03, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> The following bug has been logged on the website:
>
> Bug reference: 19652
> Logged by: chunling qin
> Email address: 303677365(at)qq(dot)com
> PostgreSQL version: 18.6
> Operating system: x86_64
> Description:
>
> When the number of integer digits exceeds what the format template
> provides,
> the excess digits are silently dropped. The very same input with a decimal
> point added raises an overflow error — one "value exceeds the format"
> scenario, two opposite behaviors:
>
> SELECT to_number('1234567', '999');
> -- 123 (digits beyond the 3-digit format silently discarded)
>
> SELECT to_number('1234567.89', '999.99');
> -- ERROR: numeric field overflow
> -- DETAIL: A field with precision 3, scale 0 must round to an absolute
> value
> -- less than 10^3.
>
> Both inputs should raise an error ("value too long" / overflow), since the
> integral part exceeds the format in both cases; silently returning 123 for
> '1234567' loses the high-order digits without any diagnostic.
>
>
Hi!

Thanks for the report.

This behavior is consistent with PostgreSQL's current to_number() semantics.

to_number() is a permissive, left-to-right parser rather than a strict
“format must fully match input” validator.
Because of that, extra input may be ignored (for example,
to_number('1234567', '999') returns 123 after consuming the first three
digit slots).

The decimal case errors for a different reason: after parsing, PostgreSQL
applies numeric typmod checks, and that step can raise numeric field
overflow. So this is not two competing validation policies; it is one
lenient parser plus a later numeric precision/scale check.

Historical context:

- to_number(text, text) entered PostgreSQL in 2000 with typmod-based
numeric conversion already in place.
- The numeric field overflow wording in numeric.c dates back to 2003, and
the detailed precision/scale message was improved in 2006.
- In 2017, pgsql-hackers discussed [0] stricter Oracle-like rejection, but
PostgreSQL deliberately kept lenient behavior for compatibility.

[0]
https://www.postgresql.org/message-id/flat/CAGMVOdvpbMqPf9XWNzOwBpzJfErkydr_fEGhmuDGa015z97mwg%40mail.gmail.com

--
Regards,
Rachitskiy Andrey