pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
numeric question..
6+ messages / 3 participants
[nested] [flat]

* numeric question..
@ 2000-01-14 16:56  Mitch Vincent <mitch@venux.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Mitch Vincent @ 2000-01-14 16:56 UTC (permalink / raw)
  To: pgsql-sql


 I have a table (invoices) that have several fields for various charges,
these fields are of type numeric(9,2).

I have a function and a trigger to total these fields on every update or
insert and place the value in the total field of the respective invoice.



CREATE FUNCTION invoice_total_trigger() RETURNS OPAQUE AS '
BEGIN
  NEW.total := NEW.fee_membership + NEW.fee_logins + NEW.fee_convention +
NEW.fee_prints_jobs
  + NEW.fee_prints_apps + NEW.fee_hotlines + NEW.fee_postage + NEW.fee_ups +
NEW.fee_late +
  NEW.fee_other1 + NEW.fee_other2 + NEW.fee_other3 + NEW.fee_pastdue -
NEW.amount_paid;
  RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER invoice_total_trigger BEFORE INSERT OR UPDATE ON invoice
FOR EACH ROW EXECUTE PROCEDURE invoice_total_trigger();


Now, this all worked fine with the fields being type float8 but I didn't
want to have to pad the numbers on display for formatting, so I decided to
use the numeric data type so that postgres would automatically store numbers
like 100 as 100.00.... Anyway, when I insert or update and the trigger calls
the function, I get this error :

Query Failed
ERROR:  overflow on numeric ABS(value) >= 10^2 for field with precision 0
scale 2

Several of the fields are over 100.00, this isn't a problem if I remove the
trigger/function and insert into the other data tables values way over
100.00 so the problem is within the function I guess...

It's simple addition and subtraction, is this a problem for the numeric
type? If it's something else small and obvious, feel free to flame me until
I die from it :-)


Thanks!!!!

-Mitch










^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] numeric question..
@ 2000-01-14 21:27  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Mitch Vincent <mitch@venux.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-01-14 21:27 UTC (permalink / raw)
  To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql

"Mitch Vincent" <mitch@venux.net> writes:
> Query Failed
> ERROR:  overflow on numeric ABS(value) >= 10^2 for field with precision 0
> scale 2

That's a known bug in the NUMERIC code --- cause not known yet, but
apparently it's somewhat platform-dependent (or maybe there's more
than one bug?).  I've been expecting Jan Wieck to produce a fix, since
NUMERIC is his turf, but he may be too busy with long-tuple support to
want to look at it.

One way or another it will get seen to before 7.0 beta, and hopefully
we can produce a back-patch for 6.5.* as well.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] numeric question..
@ 2000-01-14 21:39  Mitch Vincent <mitch@venux.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Mitch Vincent @ 2000-01-14 21:39 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Hmm, ok Tom, thanks a lot for the information..

I tried doing them in decimal(9,2) too and well, it added them as numeric
anyway -- I guess that's a dead end for a while?

Thanks again..

-Mitch


----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mitch Vincent <mitch@venux.net>
Cc: <pgsql-sql@postgreSQL.org>
Sent: Friday, January 14, 2000 4:27 PM
Subject: Re: [SQL] numeric question..


> "Mitch Vincent" <mitch@venux.net> writes:
> > Query Failed
> > ERROR:  overflow on numeric ABS(value) >= 10^2 for field with precision
0
> > scale 2
>
> That's a known bug in the NUMERIC code --- cause not known yet, but
> apparently it's somewhat platform-dependent (or maybe there's more
> than one bug?).  I've been expecting Jan Wieck to produce a fix, since
> NUMERIC is his turf, but he may be too busy with long-tuple support to
> want to look at it.
>
> One way or another it will get seen to before 7.0 beta, and hopefully
> we can produce a back-patch for 6.5.* as well.
>
> regards, tom lane
>




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] numeric question..
@ 2000-01-14 23:09  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Mitch Vincent <mitch@venux.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-01-14 23:09 UTC (permalink / raw)
  To: Mitch Vincent <mitch@venux.net>; +Cc: pgsql-sql

"Mitch Vincent" <mitch@venux.net> writes:
> I tried doing them in decimal(9,2) too and well, it added them as numeric
> anyway -- I guess that's a dead end for a while?

DECIMAL is just an alias for NUMERIC, as far as I know...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] numeric question..
@ 2000-01-16 17:14  Peter Eisentraut <peter_e@gmx.net>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Peter Eisentraut @ 2000-01-16 17:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mitch Vincent <mitch@venux.net>; pgsql-sql

On 2000-01-14, Tom Lane mentioned:

> "Mitch Vincent" <mitch@venux.net> writes:
> > I tried doing them in decimal(9,2) too and well, it added them as numeric
> > anyway -- I guess that's a dead end for a while?
> 
> DECIMAL is just an alias for NUMERIC, as far as I know...

NUMERIC specifies the data type exact numeric, with the decimal precision
and scale specified by the <precision> and <scale>.
 
DECIMAL specifies the data type exact numeric, with the decimal scale
specified by the <scale> and the implementation-defined decimal precision
equal to or greater than the value of the specified <precision>.

Up to this day I'm wondering what exactly this means ...

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] numeric question..
@ 2000-01-16 17:35  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2000-01-16 17:35 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Mitch Vincent <mitch@venux.net>; pgsql-sql

Peter Eisentraut <peter_e@gmx.net> writes:
> NUMERIC specifies the data type exact numeric, with the decimal precision
> and scale specified by the <precision> and <scale>.
 
> DECIMAL specifies the data type exact numeric, with the decimal scale
> specified by the <scale> and the implementation-defined decimal precision
> equal to or greater than the value of the specified <precision>.

> Up to this day I'm wondering what exactly this means ...

Yeah, I read that too.  I think what it means is that implementors are
allowed to increase the precision (field width) of a DECIMAL field to a
larger value if that's more efficient in their implementation, whereas
NUMERIC must have exactly the specified precision.

For example, if we were to reimplement NUMERIC using radix-10000
arithmetic (four decimal digits per 16-bit integer), then the most
efficient field widths would be multiples of 4.  According to the spec,
we'd be allowed to increase the field width of a DECIMAL field to make
it a multiple of 4, but not the width of a NUMERIC field.

Right now, there is no such implementation advantage in Postgres, so
the two field types are exactly equivalent in our code.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2000-01-16 17:35 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-01-14 16:56 numeric question.. Mitch Vincent <mitch@venux.net>
2000-01-14 21:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-14 21:39   ` Mitch Vincent <mitch@venux.net>
2000-01-14 23:09     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-16 17:14       ` Peter Eisentraut <peter_e@gmx.net>
2000-01-16 17:35         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox