Postgres Pro
Downloads
Home   >   mailing lists

Thread: Precision of calculated numeric fields

Precision of calculated numeric fields

From
Travis Bauer
Date:
Consider the following:

trbauer=# create table t1 (x numberic(3,2));
trbauer=# \d t1
Attribute |   Type       | Modifier
------------------------------------
X         | numeric(3,2) |

trbauer=# create view v1 as select x*2 from t1;
trbauer=# \d v1
Attribute |     Type             | Modifier
-------------------------------------------
?column?  | numeric(65535,65531) |

How do I get the precision on the calculated numeric field to be something
sane, like 3,2?

This is important for three reasons: 1.MSAccess chokes on views
containing these fields (citing the precision size).  2. The jdbc driver
takes _forever_ to retrieve these fields into big decimal. 3. I really
don't want to reconfigure my database to use floating points :)

Thanks,
----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

Re: Precision of calculated numeric fields

From
Tom Lane
Date:
Travis Bauer <trbauer@indiana.edu> writes:
> Consider the following:
> trbauer=# create table t1 (x numberic(3,2));
> trbauer=# \d t1
> Attribute |   Type       | Modifier
> ------------------------------------
> X         | numeric(3,2) |

> trbauer=# create view v1 as select x*2 from t1;
> trbauer=# \d v1
> Attribute |     Type             | Modifier
> -------------------------------------------
> ?column?  | numeric(65535,65531) |

> How do I get the precision on the calculated numeric field to be something
> sane, like 3,2?

You don't --- there isn't any way to specify the types of view columns.
The view is being created with typmod -1 for the numeric column, which
is correct behavior IMHO.

The bug here is in psql: it should be showing the column type as plain
"numeric", no decoration.

> This is important for three reasons: 1.MSAccess chokes on views
> containing these fields (citing the precision size).  2. The jdbc driver
> takes _forever_ to retrieve these fields into big decimal.

Sounds like the jdbc driver also gets confused when it sees typmod -1
for a numeric field.

As a short-term workaround you could manually set pg_attribute's
atttypmod column for the view's field.  Use the same value you
find in atttypmod for the underlying table's field.

            regards, tom lane

Re: Precision of calculated numeric fields

From
Travis Bauer
Date:
Is this the mod field of the Field class in JDBC2? Does -1 alway mean, for
numeric types, that the precision is undefined?  If so, I could patch up
the ResultSet class to function properly.  I traced through the
ResultSet code this morning.

----------------------------------------------------------------
Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
----------------------------------------------------------------

On Tue, 6 Jun 2000, Tom Lane wrote:

>
> Sounds like the jdbc driver also gets confused when it sees typmod -1
> for a numeric field.
>

Re: Precision of calculated numeric fields

From
Tom Lane
Date:
Travis Bauer <trbauer@indiana.edu> writes:
> Is this the mod field of the Field class in JDBC2?

Sounds right, but I haven't looked at the JDBC code.

> Does -1 alway mean, for
> numeric types, that the precision is undefined?

Right.  In general, -1 is the default typmod if Postgres doesn't have
any better idea what to put in.  So type-specific code always has to
be prepared to do something reasonable with that typmod value ...

            regards, tom lane