pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDecimal precsion?
7+ messages / 3 participants
[nested] [flat]
* Decimal precsion?
@ 1999-10-29 22:26 Zot O'Connor <zot@zotconsulting.com>
0 siblings, 1 reply; 7+ messages in thread
From: Zot O'Connor @ 1999-10-29 22:26 UTC (permalink / raw)
To: postgres sql <pgsql-sql@hub.org>
create table subzone (
subzid integer,
subzsid integer DEFAULT NEXTVAL ('subzone_seq'),
subzdescr varchar,
subztaxper decimal(0,4),
subzvendid integer,
subzwhsid integer,
subztaxship integer
);
ERROR: DECIMAL precision 0 must be beween 1 and 1000
Is this documented anywhere? By the way it should be "beTween."
--
Zot O'Connor
www.ZotConsulting.com
www.WhiteKnightHackers.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Decimal precsion?
@ 1999-10-30 00:01 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Zot O'Connor <zot@zotconsulting.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 1999-10-30 00:01 UTC (permalink / raw)
To: Zot O'Connor <zot@zotconsulting.com>; +Cc: postgres sql <pgsql-sql@hub.org>
>
> create table subzone (
> subzid integer,
> subzsid integer DEFAULT NEXTVAL ('subzone_seq'),
> subzdescr varchar,
> subztaxper decimal(0,4),
> subzvendid integer,
> subzwhsid integer,
> subztaxship integer
> );
> ERROR: DECIMAL precision 0 must be beween 1 and 1000
>
>
> Is this documented anywhere? By the way it should be "beTween."
Precision is the full length of the field. Scale is the number of
digits to the right of the decimal point.
Beween -> Between change made.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Decimal precsion?
@ 1999-10-30 00:30 Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 1999-10-30 00:30 UTC (permalink / raw)
To: Zot O'Connor <zot@zotconsulting.com>; +Cc: pgsql-sql; Jan Wieck <jwieck@debis.com>
>
> Yeah I finnally figured that out, wish the docs would cover things like
> that.
>
> I went through two books (which said its just liek COBOL! and then
> ignored me) and finnally the mysql pages had a good right up.
>
> I still thing the 0.0 thing is an error.
>
Yes, and why doesn't this generate an error:
test=> insert into example values(0.12345);
INSERT 19488 1
test=> insert into example values(0.1234567);
INSERT 19489 1
test=> select * from example;
other
------
0.1235
0.1235
(2 rows)
Jan, can you comment on this. I found the code in numeric.c, but can't
figure out what the proper test should be.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Decimal precsion?
@ 1999-10-30 15:43 Jan Wieck <wieck@debis.com>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: wieck@debis.com @ 1999-10-30 15:43 UTC (permalink / raw)
To: maillist@candle.pha.pa.us; +Cc: zot@zotconsulting.com; pgsql-sql; jwieck@debis.com
>
> >
> > Yeah I finnally figured that out, wish the docs would cover things like
> > that.
> >
> > I went through two books (which said its just liek COBOL! and then
> > ignored me) and finnally the mysql pages had a good right up.
> >
> > I still thing the 0.0 thing is an error.
> >
>
> Yes, and why doesn't this generate an error:
>
> test=> insert into example values(0.12345);
> INSERT 19488 1
> test=> insert into example values(0.1234567);
> INSERT 19489 1
> test=> select * from example;
> other
> ------
> 0.1235
> 0.1235
> (2 rows)
>
> Jan, can you comment on this. I found the code in numeric.c, but can't
> figure out what the proper test should be.
Haven't seen the original message, but it looks to me that
the column is declared as decimal with 4 digits after the
decimal point. Therefore, all values get rounded at the time
of INSERT/UPDATE. The above looks numerically right to me.
Isn't that behaviour correct? Does the standard define
something else?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Decimal precsion?
@ 1999-10-30 15:55 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Jan Wieck <wieck@debis.com>
0 siblings, 1 reply; 7+ messages in thread
From: Bruce Momjian @ 1999-10-30 15:55 UTC (permalink / raw)
To: Jan Wieck <wieck@debis.com>; +Cc: zot@zotconsulting.com; pgsql-sql; jwieck@debis.com
> >
> > >
> > > Yeah I finnally figured that out, wish the docs would cover things like
> > > that.
> > >
> > > I went through two books (which said its just liek COBOL! and then
> > > ignored me) and finnally the mysql pages had a good right up.
> > >
> > > I still thing the 0.0 thing is an error.
> > >
> >
> > Yes, and why doesn't this generate an error:
> >
> > test=> insert into example values(0.12345);
> > INSERT 19488 1
> > test=> insert into example values(0.1234567);
> > INSERT 19489 1
> > test=> select * from example;
> > other
> > ------
> > 0.1235
> > 0.1235
> > (2 rows)
> >
> > Jan, can you comment on this. I found the code in numeric.c, but can't
> > figure out what the proper test should be.
>
> Haven't seen the original message, but it looks to me that
> the column is declared as decimal with 4 digits after the
> decimal point. Therefore, all values get rounded at the time
> of INSERT/UPDATE. The above looks numerically right to me.
>
> Isn't that behaviour correct? Does the standard define
> something else?
OK, I wasn't sure on whether rounding was correct.
However, the original message had DECIMAL(4,4) and he could insert 0.1,
but not 0.0.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Decimal precsion?
@ 1999-10-30 16:37 Jan Wieck <wieck@debis.com>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: wieck@debis.com @ 1999-10-30 16:37 UTC (permalink / raw)
To: maillist@candle.pha.pa.us; +Cc: wieck@debis.com; zot@zotconsulting.com; pgsql-sql; jwieck@debis.com
> > the column is declared as decimal with 4 digits after the
> > decimal point. Therefore, all values get rounded at the time
> > of INSERT/UPDATE. The above looks numerically right to me.
> >
> > Isn't that behaviour correct? Does the standard define
> > something else?
>
> OK, I wasn't sure on whether rounding was correct.
>
> However, the original message had DECIMAL(4,4) and he could insert 0.1,
> but not 0.0.
The rounding is of course correct. Rounding 0.1234567 first
to 0.123457 and then cutting of to 0.1234 is finally cutting,
not rounding, and totally braindead. You only have to look at
the digit after the last significant one. In this case we
have 4 significant digits, so we must look at digit 5,
nothing else. Thus 0.1234500 is 0.1235 while 0.1234499 is
0.1234 - end of story.
The other one, not beeing able to insert 0.0, is surely a
bug. Would you please put it onto the TODO?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Decimal precsion?
@ 1999-10-31 13:18 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Jan Wieck <wieck@debis.com>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 1999-10-31 13:18 UTC (permalink / raw)
To: Jan Wieck <wieck@debis.com>; +Cc: zot@zotconsulting.com; pgsql-sql; jwieck@debis.com
> > However, the original message had DECIMAL(4,4) and he could insert 0.1,
> > but not 0.0.
>
> The rounding is of course correct. Rounding 0.1234567 first
> to 0.123457 and then cutting of to 0.1234 is finally cutting,
> not rounding, and totally braindead. You only have to look at
> the digit after the last significant one. In this case we
> have 4 significant digits, so we must look at digit 5,
> nothing else. Thus 0.1234500 is 0.1235 while 0.1234499 is
> 0.1234 - end of story.
>
> The other one, not beeing able to insert 0.0, is surely a
> bug. Would you please put it onto the TODO?
Done:
* insert of 0.0 into DECIMAL(4,4) field fails
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-10-31 13:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-29 22:26 Decimal precsion? Zot O'Connor <zot@zotconsulting.com>
1999-10-30 00:01 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-30 00:30 Re: [SQL] Decimal precsion? Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-30 15:43 ` Jan Wieck <wieck@debis.com>
1999-10-30 15:55 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-30 16:37 ` Jan Wieck <wieck@debis.com>
1999-10-31 13:18 ` Bruce Momjian <maillist@candle.pha.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