Обсуждение: Precision of calculated numeric fields

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

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

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

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