Psycopg difficulty...

[Jerry LeVan]

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)::numeric(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 ma*******@postgresql.org