Re: Simple math statement - problem - Mailing list pgsql-general

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