Thread: Precision and scale of numeric column reported as value -1

Bug to report - For a numeric in a table defined by:
CREATE TABLE SERG (F01NUM NUMERIC) ;
, the precision and scale reported by ResultSetMetaData.getScale() and
ResultSetMetaData.getPrecision() are value -1

Attached is a simple java program (Repro1.java) that repros the problem.

Before running repro, you must first create the table "SERG" in a
postgreSQL
database and then modify the url, userid and password in the program to
appropriate values. Notice that output from program shows precision =
-1, scale = -1

Please note that the problem occurs for NUMERIC columns whose precision
and scale are not explicitely stated. For example a column defined as
NUMERIC(10,2) returns a correct precision and scale.

Thanks, Sergio Lob

Sergio Lob wrote:
> Bug to report - For a numeric in a table defined by:
> CREATE TABLE SERG (F01NUM NUMERIC) ;
> , the precision and scale reported by ResultSetMetaData.getScale() and
> ResultSetMetaData.getPrecision() are value -1

What should they return instead in this case?

-O

Precision is the number of total digits in the number. Scale is the
number of  fractional digits.
For instance, a column defined as NUMERIC(10,3)  should return
precision=10, scale=3. The error only occurs for a column defined as
NUMERIC  (without precision or scale specified). Presumably, there is a
default precision and scale assigned to the column by postgreSQL, which
is not -1.
Sergio

Oliver Jowett wrote:

> Sergio Lob wrote:
>
>> Bug to report - For a numeric in a table defined by:
>> CREATE TABLE SERG (F01NUM NUMERIC) ;
>> , the precision and scale reported by ResultSetMetaData.getScale() and
>> ResultSetMetaData.getPrecision() are value -1
>
>
> What should they return instead in this case?
>
> -O
>

On Wed, Mar 23, 2005 at 04:13:22PM -0500, Sergio Lob wrote:
> Precision is the number of total digits in the number. Scale is the
> number of  fractional digits.
> For instance, a column defined as NUMERIC(10,3)  should return
> precision=10, scale=3. The error only occurs for a column defined as
> NUMERIC  (without precision or scale specified). Presumably, there is a
> default precision and scale assigned to the column by postgreSQL, which
> is not -1.

That assumption is wrong.  There is no default.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)

On Wed, 23 Mar 2005, Sergio Lob wrote:

> Precision is the number of total digits in the number. Scale is the
> number of  fractional digits.
> For instance, a column defined as NUMERIC(10,3)  should return
> precision=10, scale=3. The error only occurs for a column defined as
> NUMERIC  (without precision or scale specified). Presumably, there is a
> default precision and scale assigned to the column by postgreSQL, which
> is not -1.

IIRC, there is not a default precision and scale assigned to the column.
It's treated as having an unspecified precision and scale.

Sergio Lob wrote:
> Precision is the number of total digits in the number. Scale is the
> number of  fractional digits.
> For instance, a column defined as NUMERIC(10,3)  should return
> precision=10, scale=3.

Yes, I understand that.

> The error only occurs for a column defined as
> NUMERIC  (without precision or scale specified). Presumably, there is a
> default precision and scale assigned to the column by postgreSQL, which
> is not -1.

There appears to be no default, which is why we currently return -1.

Scale should default to 0 per the standard, but defaults to whatever the
precision is in PostgreSQL (see the docs for details).

The docs claim an implementation precision limit of 1000, but that
doesn't seem to correspond to the actual implementation -- I can insert
and retrieve 2000 digits NUMERICs (for example) just fine. I can't see
an obvious limit on precision in the backend code. There may be a
theoretical limit somewhere around 2^30 digits, I think (limited by the
backend's memory allocation sanity checks), but I have not tested that.

Given that there is effectively no default, do you have suggestions for
a better value to return?

-O

Oliver Jowett <oliver@opencloud.com> writes:
> There appears to be no default, which is why we currently return -1.

The spec's notion of a "default precision and scale" is that every
numeric column has a specific precision and scale --- ie, is physically
fixed-width --- and everything you store into it will be coerced to that
precision and scale.  Postgres doesn't do it that way, which is why the
notion of a default is a bit meaningless.

> Scale should default to 0 per the standard, but defaults to whatever the
> precision is in PostgreSQL (see the docs for details).

If you specify a precision only, we do assume scale 0 to go with it.
It's only the case of an unconstrained numeric column that we depart
from the spec for.

            regards, tom lane