Precision and scale of numeric column reported as value -1

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:

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