Обсуждение: 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