Menu

#79 Numeric datatype loses digits

closed-fixed
nobody
libpq (15)
5
2005-02-23
2004-10-28
Peer
No

select * from table
results in data rows. If the table includes numeric
columns digits are truncated: 4.56 will be returned as
4.6. This does not happen using a statement like this:
select * from table order by somecolumn

Discussion

  • Peer

    Peer - 2004-10-28

    Logged In: YES
    user_id=1133977

    After some investigation I discovered some more facts. You need:

    0. (Use PgSQL 1.35)
    1. A table with at least one numeric column (no precision,
    no scale)
    2. Insert data with different precision. E.g 3.1 and 4.56.
    3. SELECT all rows from db with different ordering so that
    first value (3.1) will be returned first/with second value
    (4.56) returned first.

    Then PgSQL tries to determine the precision of the numeric
    column. Since the databse does not tell it tries to guess
    (line #2859). It will guess by first value. So if 3.1 comes
    first it guesses precision is 1. BUT THIS IS WRONG. If the
    first fetched value is 4.56 it will guess precision is 2.
    Since the TypeCache uses it's knowlegde for all rows the
    guessing is sometimes wrong.

     
  • Billy G. Allie

    Billy G. Allie - 2005-02-23
    • status: open --> closed-fixed
     
  • Billy G. Allie

    Billy G. Allie - 2005-02-23

    Logged In: YES
    user_id=8500

    Changed code so that if the PgNumeric prec/scale changes,
    then it iwll use the new prec/sacle. For example:

    >>> cur.execute("""
    ... SELECT 123.4::numeric UNION
    ... SELECT 12345.4321::numeric UNION
    ... SELECT 0.00000987654321::numeric""")
    >>> cur.fetchone()
    [<PgNumeric instance - precision: 15 scale: 14 value:
    0.00000987654321>]
    >>> cur.fetchone()
    [<PgNumeric instance - precision: 4 scale: 1 value: 123.4>]
    >>> cur.fetchone()
    [<PgNumeric instance - precision: 9 scale: 4 value: 12345.4321>]
    >>>

     

Log in to post a comment.