pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped
Date: Thu, 29 Oct 2026 06:32:46 +0000
Message-ID: <19810-e14756b84da7afc8@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19810
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux x86_64
Description:        

Two independent issues in to_char() formatting.

Issue 1: float4/float8 V (decimal shift) format has no overflow check.

The V template multiplies the value by 10^n, where n is the number of digits
after V. For the float types the scale factor is computed as a double; when
10^n itself overflows, the multiplication silently produces Infinity or NaN,
and to_char() returns that as literal text.

- A perfectly representable result is destroyed:
    to_char(1e-300::float8, 'FM9V' || repeat('9', 309))  ->  "Infinity"
    The true result is 1e-300 * 10^309 = 1e9; the same format with numeric
returns 1000000000.
- Zero becomes NaN:
    to_char(0::float8, 'FM9V' || repeat('9', 309))  ->  "NaN"   (0 * inf)
    float4 already does this at n = 39.
- Ordinary overflow returns literal text instead of "#" or an error:
    to_char(1e308::float8, 'FM9V9999999')  ->  "Infinity"
    while the equivalent SQL arithmetic 1e308::float8 * 1e7 raises
    ERROR: value out of range: overflow
    and the sibling int8 path raises "bigint out of range".
    One fewer 9, to_char(1e308::float8, 'FM9V999999'), returns "#######" -
the documented overflow marker - so two adjacent format widths disagree on
how overflow is represented.
- An already-infinite input is also inconsistent within the same function:
    to_char('Infinity'::float8, 'FM9999')       ->  "####"
    to_char('Infinity'::float8, 'FM9999999999') ->  "Infinity"

The sibling branches are guarded (int4/int8 raise errors, numeric is exact);
only the float4/float8 paths lack the check.

Issue 2: template patterns after the decimal point are silently dropped,
losing the sign.

SG and PL are documented template patterns and work before or after the
digit positions. Placed after the decimal point they are silently dropped -
together with any literal text that follows - and the sign is lost:

    to_char('-100'::numeric, 'FM999')              ->  '-100'   (default
minus, correct)
    to_char('-100'::numeric, 'FM999.SG')           ->  '100'    (sign lost)
    to_char('-100'::numeric, 'FM999.SG "items"')   ->  '100'    (sign and
literal text lost)
    to_char('123.4'::numeric, 'FM999.PL')          ->  '123'    ('+' and
even the fraction dropped)
    to_char('-100'::numeric, '999.SG')             ->  '100'

Controls - the same patterns work correctly in adjacent positions:

    to_char('-100'::numeric, 'FMSG999')    ->  '-100'
    to_char('123.4'::numeric, 'FM999PL')   ->  '123.4+'
    to_char('-100'::numeric, 'FM999MI')    ->  '100-'

A negative number formatted with a format string that contains a sign
pattern (SG) must not render as an unsigned positive number.








view thread (2+ messages)  latest in thread

Message-ID: <19810-e14756b84da7afc8@postgresql.org>
Permalink:  ../19810-e14756b84da7afc8@postgresql.org/
Also on:    postgresql.org/message-id/19810-e14756b84da7afc8@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19810: to_char(): float V-format overflow yields 'Infinity' or NaN; format after decimal point dropped
  In-Reply-To: <19810-e14756b84da7afc8@postgresql.org>
  References: <19810-e14756b84da7afc8@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox