The problem turned out to be related to my function.. Given this table: CREATE TABLE "table2" ( "s_val" numeric(6,2), "e_val" numeric(6,2) ) WITH OIDS; The following functions of code will set retval = NULL; declare retval numeric(6,2); rec record; begin SELECT * INTO rec FROM table2 LIMIT 0; rec.s_val = 100; rec.e_val = 101; retval = (rec.s_val - rec.e_val) / rec.s_val; return retval; end However, if I explicitly typecast, then it returns the proper value: retval = (rec.s_val::numeric(6,2) - rec.e_val::numeric(6,2)) / rec.s_val::numeric(6,2); On Nov 29, 2007 9:47 PM, Gregory Williamson <Gregory.Williamson@xxxxxxxxxxxxxxxx> wrote: > > > A quick experiment shows that if either numerator or denominator are > decimal, that is preserved in the end result. Probably true for basic math > operations in general. > > GW > > > > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx on behalf of Gregory Williamson > Sent: Thu 11/29/2007 10:37 PM > To: Postgres User; pgsql-general > Subject: Re: Simple math statement - problem > > The question: > > > > How can I write statements that returns a decimal? > > > > > billing=# select 1/100; > ?column? > ---------- > 0 > (1 row) > > As you said ... > > So make everything decimal: > billing=# select 1.0/100.0; > ?column? > ------------------------ > 0.01000000000000000000 > > Or: > billing=# select 1::decimal/100::decimal; > ?column? > ------------------------ > 0.01000000000000000000 > > I think that when you use integers you lose precision right out the gate. > Others can provide better insight I hope ... > > HTH, > > Greg Williamson > Senior DBA > GlobeXplorer LLC, a DigitalGlobe company > > Confidentiality Notice: This e-mail message, including any attachments, is > for the sole use of the intended recipient(s) and may contain confidential > and privileged information and must be protected in accordance with those > provisions. Any unauthorized review, use, disclosure or distribution is > prohibited. If you are not the intended recipient, please contact the sender > by reply e-mail and destroy all copies of the original message. > > (My corporate masters made me say this.) > > > > > -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx on behalf of Gregory Williamson > Sent: Thu 11/29/2007 10:37 PM > To: Postgres User; pgsql-general > Subject: Re: Simple math statement - problem > > The question: > > > > How can I write statements that returns a decimal? > > > > > billing=# select 1/100; > ?column? > ---------- > 0 > (1 row) > > As you said ... > > So make everything decimal: > billing=# select 1.0/100.0; > ?column? > ------------------------ > 0.01000000000000000000 > > Or: > billing=# select 1::decimal/100::decimal; > ?column? > ------------------------ > 0.01000000000000000000 > > I think that when you use integers you lose precision right out the gate. > Others can provide better insight I hope ... > > HTH, > > Greg Williamson > Senior DBA > GlobeXplorer LLC, a DigitalGlobe company > > Confidentiality Notice: This e-mail message, including any attachments, is > for the sole use of the intended recipient(s) and may contain confidential > and privileged information and must be protected in accordance with those > provisions. Any unauthorized review, use, disclosure or distribution is > prohibited. If you are not the intended recipient, please contact the sender > by reply e-mail and destroy all copies of the original message. > > (My corporate masters made me say this.) > > > > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
