Psycopg difficulty...

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Jerry LeVan

    #1

    Psycopg difficulty...

    Psycopg and probably PySQL seem to have decided to return
    a "float" type when libpq returns a string tagged with
    the "numeric" property.

    This can cause "pretty" printing problems when generating
    reports. ( I like all of my decimal points to line up.)

    For example in my python based browser if I try the
    command

    select 123.4500

    I will get

    123.45

    and I have lost the scale. More over the description
    attribute for the cursor will contain 2^16 -1 for the
    precision and scale so that there is no way to recover
    the lost scale factor.

    For "regular" columns that have been declared with
    a numeric(9,2) attribute (as an example). then a select
    will leave in the cursor column description the proper
    values for the precision and scale and I can generate
    correct looking reports.

    However for "computed" columns in something like

    SELECT oid, *, (SELECT sum(amount)
    FROM checks WHERE x.oid >= oid ) AS total
    FROM checks x
    ORDER BY date,oid ;

    Assuming that amount is declared with numeric(9,2)
    the "total" column will have dropped any trailing
    zeros (ie 19.70 will display as 19.7).

    Basically computed columns do not furnish any
    info as to scale and precision

    The only way I can see to get around the problem is
    to cast the "total" column with the desired precision.

    SELECT oid, *, (SELECT sum(amount)::nu meric(9,2)
    FROM checks WHERE x.oid >= oid ) AS total
    FROM checks x
    ORDER BY date,oid ;

    But I don't have to do the casting using libpq,pgsql,tcl ,
    or perl-dbi interface.

    It seems like the right thing to do is to return the
    string value and let the user do the formatting
    like all of the other interfaces do...

    Jerry


    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

Working...