Обсуждение: Numeric Data Type Rounding Up

I have a table called weight and this table is tracking weight for a
few people in a fitness program that just started. I created the table
as described below:

fitness=# \d weight
                                Table "public.weight"
 Column |         Type          |                      Modifiers
--------+-----------------------+-----------------------------------------------------
 id     | integer               | not null default
nextval('weight_id_seq'::regclass)
 lbs    | numeric(5,0)          | not null
 date   | date                  | not null
 dow    | character varying(9)  | not null
 name   | character varying(50) | not null
Indexes:
    "weight_pkey" PRIMARY KEY, btree (id)

Now when I enter a value in the 'lbs' field / column of '172.80', it
rounds the value up to '173.00'. I looked on the documentation and
found a numeric data type called 'real'. I tried changing the data
type from 'NUMERIC' to 'REAL' but it didn't work. I honestly don't
know if that is even the correct numeric data type I want / need to
show exact values on something like tracking weight figures. Can
someone tell me if I'm way off here?

Carlos Mennens <carlos.mennens@gmail.com> writes:
> I have a table called weight and this table is tracking weight for a
> few people in a fitness program that just started. I created the table
> as described below:

> fitness=# \d weight
> ...
>  lbs    | numeric(5,0)          | not null

> Now when I enter a value in the 'lbs' field / column of '172.80', it
> rounds the value up to '173.00'.

Well, you told it to: (5,0) says five digits before the decimal point
and none after.  Perhaps numeric(5,2) is what you were after.

            regards, tom lane

On Wed, Mar 7, 2012 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Well, you told it to: (5,0) says five digits before the decimal point
> and none after.  Perhaps numeric(5,2) is what you were after.

I can't believe I missed that. I feel really dumb not realizing that.

Thank you.

On 03/07/2012 08:50 AM, Carlos Mennens wrote:
> I have a table...
>                                  Table "public.weight"
>   Column |         Type          |                      Modifiers
> --------+-----------------------+-----------------------------------------------------
> ...
>   lbs    | numeric(5,0)          | not null
> ...
> Now when I enter a value in the 'lbs' field / column of '172.80', it
> rounds the value up to '173.00'.
You specified a precision of 0. Try 2 (if you really want to track to
the 100th of a pound) or 1 for 1/10 pound. numeric(5,2) will be good up
to 999.99 pounds.

steve=# select 123.45::numeric(5,0);
  numeric
---------
      123

steve=# select 123.45::numeric(5,1);
  numeric
---------
    123.5

steve=# select 123.45::numeric(5,2);
  numeric
---------
   123.45

Cheers,
Steve