pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednumeric question..
6+ messages / 3 participants
[nested] [flat]
* numeric question..
@ 2000-01-14 16:56 Mitch Vincent <mitch@venux.net>
2000-01-14 21:27 ` Re: [SQL] numeric question.. Tom Lane <tgl@sss.pgh.pa.us>
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 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 ` Re: [SQL] numeric question.. 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 16:56 numeric question.. Mitch Vincent <mitch@venux.net>
2000-01-14 21:27 ` Re: [SQL] numeric question.. Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-14 21:39 ` Mitch Vincent <mitch@venux.net>
2000-01-14 23:09 ` Re: [SQL] numeric question.. 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 16:56 numeric question.. Mitch Vincent <mitch@venux.net>
2000-01-14 21:27 ` Re: [SQL] numeric question.. Tom Lane <tgl@sss.pgh.pa.us>
2000-01-14 21:39 ` Re: [SQL] numeric question.. Mitch Vincent <mitch@venux.net>
@ 2000-01-14 23:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-01-16 17:14 ` Re: [SQL] numeric question.. Peter Eisentraut <peter_e@gmx.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-14 16:56 numeric question.. Mitch Vincent <mitch@venux.net>
2000-01-14 21:27 ` Re: [SQL] numeric question.. Tom Lane <tgl@sss.pgh.pa.us>
2000-01-14 21:39 ` Re: [SQL] numeric question.. Mitch Vincent <mitch@venux.net>
2000-01-14 23:09 ` Re: [SQL] numeric question.. Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-01-16 17:14 ` Peter Eisentraut <peter_e@gmx.net>
2000-01-16 17:35 ` Re: [SQL] numeric question.. 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-14 16:56 numeric question.. Mitch Vincent <mitch@venux.net>
2000-01-14 21:27 ` Re: [SQL] numeric question.. Tom Lane <tgl@sss.pgh.pa.us>
2000-01-14 21:39 ` Re: [SQL] numeric question.. Mitch Vincent <mitch@venux.net>
2000-01-14 23:09 ` Re: [SQL] numeric question.. Tom Lane <tgl@sss.pgh.pa.us>
2000-01-16 17:14 ` Re: [SQL] numeric question.. Peter Eisentraut <peter_e@gmx.net>
@ 2000-01-16 17:35 ` Tom Lane <tgl@sss.pgh.pa.us>
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