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
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html style="direction: ltr;">
<head>
<meta content="text/html;charset=windows-1255"
http-equiv="Content-Type">
</head>
<body style="direction: ltr;" bgcolor="#ffffff" text="#000000">
<br>
<blockquote cite="mid:6844.1255969297@sss.pgh.pa.us" type="cite">
<blockquote type="cite">
<pre wrap="">Numeric with scale precision always shows the trailing zeros.
Numeric plain only shows numbers after the decimal point that are being
used.
</pre>
</blockquote>
<pre wrap=""><!---->
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
</pre>
</blockquote>
According to the documentation, numeric is stored without any leading
or trailing zeros.<br>
<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/current/static/datatype-numeric.html">http://www.postgresql.org/docs/current/static/datatype-numeric.html</a><br>
<blockquote type="cite">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 <tt class="TYPE">numeric</tt>
type is more akin to <tt class="TYPE">varchar(<tt class="REPLACEABLE"><i>n</i></tt>)</tt>
than to <tt class="TYPE">char(<tt class="REPLACEABLE"><i>n</i></tt>)</tt>.)
The actual storage requirement is two bytes for each group of four
decimal digits, plus eight bytes overhead.</blockquote>
However, in practice:<br>
create table test(f1 numeric);<br>
insert into test(f1)values(15.000);<br>
select * from test;<br>
f1<br>
-------<br>
15.000<br>
<br>
<br>
</body>
</html>
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.htmlNumeric 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