Thread: Simple math statement - problem

Simple math statement - problem

From

"Postgres User"

Date:

30 November 2007, 04:24:18

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?


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)

Re: Simple math statement - problem

From

Ow Mun Heng

Date:

30 November 2007, 04:37:18

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

Re: Simple math statement - problem

From

"Gregory Williamson"

Date:

30 November 2007, 04:40:08

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

Re: Simple math statement - problem

From

"Gregory Williamson"

Date:

30 November 2007, 04:48:57

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

Re: Simple math statement - problem

From

"Postgres User"

Date:

30 November 2007, 07:04:08

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

Re: Simple math statement - problem

From

Lew

Date:

02 December 2007, 17:06:07

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

Re: Simple math statement - problem

From

Lew

Date:

02 December 2007, 17:06:09

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