Re: BUG #16919: Unexpected precision loss in NUMERIC type during text cast, math operations

I was banging my head against a wall for a while trying to debug this,
because this *is* a stock Postgres (PGDG APT Postgres 13.2), as are all the
installed extensions (also from PGDG APT.) Also, I couldn't replicate the
problem even on the same machine in a secondary cluster.

But then, as a sanity check, I tried the same query in psql on the
*original* cluster, and it was fine...

It turns out it was actually the *client* I was originally using for
testing my queries (Postico) that was losing precision! The query was fine!

I could have figured this out sooner if I had just done a simple round-trip
test:

SELECT '1234567890123456789012345678901234567890'::numeric::text;

That, of course, gives the right result, even in Postico — demonstrating
that it was never NUMERIC's fault.

Time for a bug report to Postico...

On Wed, Mar 10, 2021 at 2:00 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
> > Simple operations on NUMERIC, supposedly an arbitrary-precision type, are
> > having unexpected results for me:
>
> > SELECT '12345678901234567890123456789012345678901234567890'::numeric;
> > -- expected 12345678901234567890123456789012345678901234567890
> > -- got 12345678901234567890123456789012345678900000000000
>
> [ raised eyebrow... ] Works for me:
>
> regression=# SELECT
> '12345678901234567890123456789012345678901234567890'::numeric;
> numeric
> ----------------------------------------------------
> 12345678901234567890123456789012345678901234567890
> (1 row)
>
> I'm wondering which compiler and what build options you used.
> Also, your reference to mpz makes me doubt that this is a stock
> version of Postgres, so maybe you just have a home-grown bug.
>
> (There are some arbitrary decisions about precision of the results
> of numeric division, as well as the transcendental functions.
> But plain addition and multiplication should be exact, and of course
> just reading out a literal certainly should be.)
>
> regards, tom lane
>