Re: cast numeric with scale and precision to numeric plain

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(at)comcast(dot)net