Psycopg difficulty...

From: Jerry LeVan <jerry(dot)levan(at)eku(dot)edu>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Psycopg difficulty...
Date: 2004-10-27 15:40:17
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

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

select 123.4500

I will get


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...


Browse pgsql-general by date

  From Date Subject
Next Message nd02tsk 2004-10-27 15:56:16 Reasoning behind process instead of thread based arch?
Previous Message NTPT 2004-10-27 15:33:03 Exact or less specific match ?