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

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

The following bug has been logged on the website:

Bug reference: 19652
Logged by: chunling qin
Email address: 303677365@qq.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]/messages/by-id/CAGMVOdvpbMqPf9XWNzOwBpzJfErkydr_fEGhmuDGa015z97mwg@mail.gmail.com stricter Oracle-like rejection, but
PostgreSQL deliberately kept lenient behavior for compatibility.

[0]: /messages/by-id/CAGMVOdvpbMqPf9XWNzOwBpzJfErkydr_fEGhmuDGa015z97mwg@mail.gmail.com
/messages/by-id/CAGMVOdvpbMqPf9XWNzOwBpzJfErkydr_fEGhmuDGa015z97mwg@mail.gmail.com

--
Regards,
Rachitskiy Andrey