| From: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: cast numeric with scale and precision to numeric plain |
| Date: | 2009-10-20 10:39:22 |
| Message-ID: | 4ADD935A.8060601@compulab.co.il |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
<!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(dot)1255969297(at)sss(dot)pgh(dot)pa(dot)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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 2.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Grzegorz Jaśkiewicz | 2009-10-20 10:44:43 | Re: Free Tool to design Postgres Databases |
| Previous Message | Grzegorz Jaśkiewicz | 2009-10-20 10:36:19 | Re: Free Tool to design Postgres Databases |