Re: [GENERAL] Simple math statement - problem
Postgres User wrote: 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; I am curious what would happen if you wrote your procedure like this: declare retval numeric(6,2); rec table2%ROWTYPE; begin rec.s_val = 100; rec.e_val = 101; retval = (rec.s_val - rec.e_val) / rec.s_val; return retval; end Also, one wonders why you need to do the calculation via a row or record at all, when it would seem so easy just to plug in the values. -- Lew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Simple math statement - problem
Lew wrote: Postgres User wrote: 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); Sure, because the first way you're doing integer division, and the second way you're doing floating point division. In integer division, -1/100 yields zero. The more I look at this, the more I think I'm wrong. I'm researching the semantics of the idioms that you used. I don't know what type rec.s_val and rec.e_val end up being after the integer assignments. -- Lew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Simple math statement - problem
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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] on behalf of Gregory Williamson Sent: Thu 11/29/2007 10:37 PM To: Postgres User; pgsql-general Subject: Re: [GENERAL] 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.0100 Or: billing=# select 1::decimal/100::decimal; ?column? 0.0100 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: [EMAIL PROTECTED] on behalf of Gregory Williamson Sent: Thu 11/29/2007 10:37 PM To: Postgres User; pgsql-general Subject: Re: [GENERAL] 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.0100 Or: billing=# select 1::decimal/100::decimal; ?column? 0.0100 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
Re: [GENERAL] Simple math statement - problem
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: [EMAIL PROTECTED] on behalf of Gregory Williamson Sent: Thu 11/29/2007 10:37 PM To: Postgres User; pgsql-general Subject: Re: [GENERAL] 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.0100 Or: billing=# select 1::decimal/100::decimal; ?column? 0.0100 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.)
Re: [GENERAL] 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.0100 Or: billing=# select 1::decimal/100::decimal; ?column? 0.0100 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.)
Re: [GENERAL] Simple math statement - problem
On Thu, 2007-11-29 at 21:22 -0800, Postgres User wrote: I have a large function that's doing a number of calcs. The final return value is wrong for a simple reason: any division statement where the numerator is less than the denominator is returning a zero. Each of these statements return a 0, even when properly cast: select 1/100 select Cast(1 / 100 As decimal) select Cast(1 / 100 As numeric(6,2)) How can I write statements that returns a decimal? select (1::numeric/100::numeric) same as if you do a 1.0/100.0 The problem doesn't appear to be that Postgres won't return decimal values, as these statements return the correct value: select .01 select Cast(.01 As decimal) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
