pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Decimal 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