Skip site navigation (1) Skip section navigation (2)

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
Message-ID: (view raw, whole thread or download thread mbox)
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...


pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group