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 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.)

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@postgresql.org 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.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.)

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@digitalglobe.com> wrote:

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

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