Обсуждение: cast numeric with scale and precision to numeric plain
I'm using 8.2.4 Numeric with scale precision always shows the trailing zeros. Numeric plain only shows numbers after the decimal point that are being used. I would like to have the data in my table with scale and precision, but my views to be cast to numeric without any scale or precision. However, when I cast a numeric(16,4) to a ::numeric it doesn't cast it. In order to get the results I want, I have to cast it twice ::double precision::numeric and then it removes the trailing zeros. Is there any way to cast directly from numeric with scale and precision to plain numeric? example: create table test(f1 numeric(16,4)); insert into test(f1)values(15),(200.004),(12.4123); select f1 from test; 15.0000 200.0040 12.4123 select f1::numeric from test 15.0000 200.0040 12.4123 select f1::float::numeric from test 15 200.004 12.4123
Sim Zacks <sim@compulab.co.il> writes:
> Numeric with scale precision always shows the trailing zeros.
> Numeric plain only shows numbers after the decimal point that are being
> used.
That statement is false:
regression=# select 1234.000::numeric;
numeric
----------
1234.000
(1 row)
I'm not sure offhand what is the easiest way to suppress trailing
zeroes, but casting to plain numeric is not the route to a solution.
Really this is a textual formatting problem. You might find that the
best bet is something with trim() or a regexp. The trick would be
not removing zeroes that are significant ...
regards, tom lane
<meta content="text/html;charset=windows-1255"
http-equiv="Content-Type">
Numeric with scale precision always shows the trailing zeros.
Numeric plain only shows numbers after the decimal point that are being
used.
That statement is false:
regression=# select 1234.000::numeric;
numeric
----------
1234.000
(1 row)
I'm not sure offhand what is the easiest way to suppress trailing
zeroes, but casting to plain numeric is not the route to a solution.
Really this is a textual formatting problem. You might find that the
best bet is something with trim() or a regexp. The trick would be
not removing zeroes that are significant ...
regards, tom lane
According to the documentation, numeric is stored without any leading
or trailing zeros.
http://www.postgresql.org/docs/current/static/datatype-numeric.html
Numeric values are physically stored without
any extra leading or
trailing zeroes. Thus, the declared precision and scale of a column are
maximums, not fixed allocations. (In this sense the numeric
type is more akin to varchar(n)
than to char(n).)
The actual storage requirement is two bytes for each group of four
decimal digits, plus eight bytes overhead.
However, in practice:
create table test(f1 numeric);
insert into test(f1)values(15.000);
select * from test;
f1
-------
15.000
On Tuesday 20 October 2009 3:39:22 am Sim Zacks wrote: > Numeric with scale precision always shows the trailing zeros. > Numeric plain only shows numbers after the decimal point that are being > used. > > > That statement is false: > > regression=# select 1234.000::numeric; > numeric > ---------- > 1234.000 > (1 row) > > I'm not sure offhand what is the easiest way to suppress trailing > zeroes, but casting to plain numeric is not the route to a solution. > > Really this is a textual formatting problem. You might find that the > best bet is something with trim() or a regexp. The trick would be > not removing zeroes that are significant ... > > regards, tom lane > > According to the documentation, numeric is stored without any leading or > trailing zeros. > http://www.postgresql.org/docs/current/static/datatype-numeric.html > > Numeric values are physically stored without any extra leading or trailing > zeroes. Thus, the declared precision and scale of a column are maximums, > not fixed allocations. (In this sense the numeric type is more akin to > varchar(n) than to char(n).) The actual storage requirement is two bytes > for each group of four decimal digits, plus eight bytes overhead. However, > in practice: > create table test(f1 numeric); > insert into test(f1)values(15.000); > select * from test; > f1 > ------- > 15.000 The part of the above that you need to look at is where it says it does not store 'any extra leading or trailing zeroes'. In your case you entered the value with three trailing zeroes which are taken to be significant (see Toms reply also). If you had inserted just 15 you would have gotten back 15. -- Adrian Klaver aklaver@comcast.net
Sim Zacks <sim@compulab.co.il> writes:
>>> I'm not sure offhand what is the easiest way to suppress trailing
>>> zeroes, but casting to plain numeric is not the route to a solution.
> According to the documentation, numeric is stored without any leading
> or trailing zeros.
That says *stored*; it doesn't say *displayed*.
regards, tom lane
>> According to the documentation, numeric is stored without any leading >> or trailing zeros. >> > > That says *stored*; it doesn't say *displayed*. > > regards, tom lane > If it displays them, it has to know they exist. That means it stores it somewhere. > The part of the above that you need to look at is where it says it does not > store 'any extra leading or trailing zeroes'. In your case you entered the > value with three trailing zeroes which are taken to be significant (see Toms > reply also). If you had inserted just 15 you would have gotten back 15. > I guess that's a matter of interpretation. To me zeros after a decimal point without anything else after them are extra.
2009/10/20 Sim Zacks <sim@compulab.co.il>: > I guess that's a matter of interpretation. To me zeros after a decimal > point without anything else after them are extra. From a mathematical perspective, they most certainly are not extra. 15.000 15.001 15.002 15.003 Each have three digits of precision, and each mean something here.
----- "Sim Zacks" <sim@compulab.co.il> wrote: > >> According to the documentation, numeric is stored without any > leading > >> or trailing zeros. > >> > > > > That says *stored*; it doesn't say *displayed*. > > > > regards, tom lane > > > If it displays them, it has to know they exist. That means it stores > it > somewhere. > > The part of the above that you need to look at is where it says it > does not > > store 'any extra leading or trailing zeroes'. In your case you > entered the > > value with three trailing zeroes which are taken to be significant > (see Toms > > reply also). If you had inserted just 15 you would have gotten back > 15. > > > I guess that's a matter of interpretation. To me zeros after a > decimal > point without anything else after them are extra. See below for an explanation of why the above statement is incorrect. http://en.wikipedia.org/wiki/Significant_figures Adrian Klaver aklaver@comcast.net
Sim Zacks wrote: > I'm using 8.2.4 > > > Numeric with scale precision always shows the trailing zeros. > > Numeric plain only shows numbers after the decimal point that are being > used. > > > I would like to have the data in my table with scale and precision, but > my views to be cast to numeric without any scale or precision. > > However, when I cast a numeric(16,4) to a ::numeric it doesn't cast it. > > > In order to get the results I want, I have to cast it twice ::double > precision::numeric and then it removes the trailing zeros. > > > Is there any way to cast directly from numeric with scale and precision > to plain numeric? So you already got your answer about why it doesn't work the way you'd like. But if you are just looking to format it for display you can use to_char with the FM modifier. SELECT f1, to_char(f1, 'FM999.0999') FROM test; 15.0000 15.0 200.0040 200.004 12.4123 12.4123