> Hi all,
>
> I'm trying numeric & decimal types in v6.5beta1 and I have two questions
> about it.
>
> [...]
>
> Second question:
>     Why PostgreSQL allows to insert 14 digits into a numeric(5,1) ?
>
> create table test(
>         n numeric(10,3),
>         d decimal(5,1)
> );

    For  some reason (dunno why) the parser ignores the precision
    for DECIMAL.  atttypmod is set hardcoded to -1.  So the above
    is identical to a

        CREATE TABLE test (n numeric(10,3), d decimal);

    I'll  test  what  happens  if I enable it in gram.y and if it
    doesn't break any regression commit the changes.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Tue, 27 Apr 1999 13:40:47 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] numeric & decimal
I wrote:

>     For  some reason (dunno why) the parser ignores the precision
>     for DECIMAL.  atttypmod is set hardcoded to -1.  So the above
>     is identical to a
>
>         CREATE TABLE test (n numeric(10,3), d decimal);
>
>     I'll  test  what  happens  if I enable it in gram.y and if it
>     doesn't break any regression commit the changes.

    It doesn't and I did.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Tue, 27 Apr 1999 15:37:45 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] numeric & decimal
>     For  some reason (dunno why) the parser ignores the precision
>     for DECIMAL.  atttypmod is set hardcoded to -1.  So the above
>     is identical to a
> 
>         CREATE TABLE test (n numeric(10,3), d decimal);
> 
>     I'll  test  what  happens  if I enable it in gram.y and if it
>     doesn't break any regression commit the changes.

In the old days, we couldn't handle precision, so we ignored it.

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

Tue, 27 Apr 1999 12:21:55 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] numeric & decimal
Jan Wieck ha scritto:

> > Hi all,
> >
> > I'm trying numeric & decimal types in v6.5beta1 and I have two questions
> > about it.
> >
> > [...]
> >
> > Second question:
> >     Why PostgreSQL allows to insert 14 digits into a numeric(5,1) ?
> >
> > create table test(
> >         n numeric(10,3),
> >         d decimal(5,1)
> > );
>
>     For  some reason (dunno why) the parser ignores the precision
>     for DECIMAL.  atttypmod is set hardcoded to -1.  So the above
>     is identical to a
>
>         CREATE TABLE test (n numeric(10,3), d decimal);
>
>     I'll  test  what  happens  if I enable it in gram.y and if it
>     doesn't break any regression commit the changes.
>
> Jan
>

Great!
I have other questions about NUMERICs:

> create table test(
>         num0 numeric,
>         num1 numeric(1),
>         num4 numeric(4,1)
> );
> CREATE
> insert into test values (11111111,11111111,-9,9,-999.99,-999.99);
> INSERT 78190 1
> select * from test;
>            num0|num1|   num4
> ---------------+----+-------
> 11111111.000000|   9|-1000.0
>                   ^^^^^^            ^^^^^^^

- I don't understand this default:
  NUMERIC  without size is interpreted as NUMERIC(x,6). Why ?
  Standard SQL92 says that NUMERIC without size is equivalent to NUMERIC(1)

- NUMERIC(4,1)  transalte value -999.99 as -1000.0 (greater than his size)

Comments?

Josï¿½



Jan Wieck ha scritto:
> Hi all,
>
> I'm trying numeric & decimal types in v6.5beta1 and I have
two questions
> about it.
>
> [...]
>
> Second question:
>     Why PostgreSQL allows to insert 14 digits
into a numeric(5,1) ?
>
> create table test(
>         n numeric(10,3),
>         d decimal(5,1)
> );
    For  some reason (dunno why) the parser
ignores the precision
    for DECIMAL.  atttypmod is set hardcoded
to -1.  So the above
    is identical to a
        CREATE TABLE test (n
numeric(10,3), d decimal);
    I'll  test  what  happens 
if I enable it in gram.y and if it
    doesn't break any regression commit the changes.
Jan
 
Great!
I have other questions about NUMERICs:
create table test(
        num0 numeric,
        num1 numeric(1),
        num4 numeric(4,1)
);
CREATE
insert into test values (11111111,11111111,-9,9,-999.99,-999.99);
INSERT 78190 1
select * from test;
           num0|num1|  
num4
---------------+----+-------
11111111.000000|   9|-1000.0
                 
^^^^^^           
^^^^^^^
- I don't understand this default:
  NUMERIC  without size is interpreted as NUMERIC(x,6). Why
?
  Standard SQL92 says that NUMERIC without size is equivalent
to NUMERIC(1)
- NUMERIC(4,1)  transalte value -999.99 as -1000.0 (greater than
his size)
Comments?
José
Wed, 28 Apr 1999 15:02:36 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] numeric & decimal

This looks like something that should be addressed.  Was it?



[Charset iso-8859-1 unsupported, filtering to ASCII...]
> Jan Wieck ha scritto:
> 
> > > Hi all,
> > >
> > > I'm trying numeric & decimal types in v6.5beta1 and I have two questions
> > > about it.
> > >
> > > [...]
> > >
> > > Second question:
> > >     Why PostgreSQL allows to insert 14 digits into a numeric(5,1) ?
> > >
> > > create table test(
> > >         n numeric(10,3),
> > >         d decimal(5,1)
> > > );
> >
> >     For  some reason (dunno why) the parser ignores the precision
> >     for DECIMAL.  atttypmod is set hardcoded to -1.  So the above
> >     is identical to a
> >
> >         CREATE TABLE test (n numeric(10,3), d decimal);
> >
> >     I'll  test  what  happens  if I enable it in gram.y and if it
> >     doesn't break any regression commit the changes.
> >
> > Jan
> >
> 
> Great!
> I have other questions about NUMERICs:
> 
> > create table test(
> >         num0 numeric,
> >         num1 numeric(1),
> >         num4 numeric(4,1)
> > );
> > CREATE
> > insert into test values (11111111,11111111,-9,9,-999.99,-999.99);
> > INSERT 78190 1
> > select * from test;
> >            num0|num1|   num4
> > ---------------+----+-------
> > 11111111.000000|   9|-1000.0
> >                   ^^^^^^            ^^^^^^^
> 
> - I don't understand this default:
>   NUMERIC  without size is interpreted as NUMERIC(x,6). Why ?
>   Standard SQL92 says that NUMERIC without size is equivalent to NUMERIC(1)
> 
> - NUMERIC(4,1)  transalte value -999.99 as -1000.0 (greater than his size)
> 
> Comments?
> 
> Jos_
> 


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

Mon, 10 May 1999 12:37:04 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] numeric & decimal
Bruce Momjian wrote:
>
>
> This looks like something that should be addressed.  Was it?
>
>
>
> > >     For  some reason (dunno why) the parser ignores the precision
> > >     for DECIMAL.  atttypmod is set hardcoded to -1.  So the above
> > >     is identical to a
> > >
> > >         CREATE TABLE test (n numeric(10,3), d decimal);
> > >
> > >     I'll  test  what  happens  if I enable it in gram.y and if it
> > >     doesn't break any regression commit the changes.

    This  one  is  fixed.  Parser  handles  precision  of DECIMAL
    already.

> >   NUMERIC  without size is interpreted as NUMERIC(x,6). Why ?
> >   Standard SQL92 says that NUMERIC without size is equivalent to NUMERIC(1)

    PostgreSQL specific. Should I change it to standard?

> >
> > - NUMERIC(4,1)  transalte value -999.99 as -1000.0 (greater than his size)

    Definitely a bug. The value is checked before  the  rounding.
    Will fix it soon.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 10 May 1999 18:51:02 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] numeric & decimal
> > > NUMERIC  without size is interpreted as NUMERIC(x,6). Why ?
> > > Standard SQL92 says that NUMERIC without size is equivalent
> > > to NUMERIC(1)
>     PostgreSQL specific. Should I change it to standard?

The standard (per Date's book) is:

  NUMERIC == NUMERIC(p), where p is implementation-defined.
  NUMERIC(p) == NUMERIC(p,0)

Date also explicitly says that:

  "The following are implementation-defined:
  ...
  o The default precision for NUMERIC and DECIMAL if there is no
    declared precision
  ..."

So where did NUMERIC(1) come from? afaict Jan should use what he feels
are reasonable values...

                     - Tom

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Mon, 10 May 1999 17:58:30 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] numeric & decimal
>
> > > > NUMERIC  without size is interpreted as NUMERIC(x,6). Why ?
> > > > Standard SQL92 says that NUMERIC without size is equivalent
> > > > to NUMERIC(1)
> >     PostgreSQL specific. Should I change it to standard?
>
> The standard (per Date's book) is:
>
>   NUMERIC == NUMERIC(p), where p is implementation-defined.
>   NUMERIC(p) == NUMERIC(p,0)
>
> Date also explicitly says that:
>
>   "The following are implementation-defined:
>   ...
>   o The default precision for NUMERIC and DECIMAL if there is no
>     declared precision
>   ..."
>
> So where did NUMERIC(1) come from? afaict Jan should use what he feels
> are reasonable values...

    The  default  for  NUMERIC  is  NUMERIC(30,6).  NUMERIC(n) is
    treated as NUMERIC(n,0). So it is exactly as  Date  says  and
    since  it  is  already  released,  nothing  to  get changed -
    period.

    If someone want's his  installation  to  act  different,  the
    place     to     do    it    is    include/numeric.h    where
    NUMERIC_DEFAULT_PRECISION  and  NUMERIC_DEFAULT_SCALE  define
    the two values.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 10 May 1999 20:27:27 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] numeric & decimal

Cool, item removed.

> > > > NUMERIC  without size is interpreted as NUMERIC(x,6). Why ?
> > > > Standard SQL92 says that NUMERIC without size is equivalent
> > > > to NUMERIC(1)
> >     PostgreSQL specific. Should I change it to standard?
> 
> The standard (per Date's book) is:
> 
>   NUMERIC == NUMERIC(p), where p is implementation-defined.
>   NUMERIC(p) == NUMERIC(p,0)
> 
> Date also explicitly says that:
> 
>   "The following are implementation-defined:
>   ...
>   o The default precision for NUMERIC and DECIMAL if there is no
>     declared precision
>   ..."
> 
> So where did NUMERIC(1) come from? afaict Jan should use what he feels
> are reasonable values...
> 
>                      - Tom
> 
> -- 
> Thomas Lockhart				lockhart@alumni.caltech.edu
> South Pasadena, California
> 


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

Mon, 10 May 1999 14:38:26 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] numeric & decimal
> > > - NUMERIC(4,1)  transalte value -999.99 as -1000.0 (greater than his size)
>
>     Definitely a bug. The value is checked before  the  rounding.
>     Will fix it soon.

    Fixed - value is now checked again after rounding.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Mon, 10 May 1999 20:43:27 +0200 (MET DST)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] numeric & decimal
> >   NUMERIC == NUMERIC(p), where p is implementation-defined.
> >   NUMERIC(p) == NUMERIC(p,0)
> >   "The following are implementation-defined:
> >   o The default precision for NUMERIC and DECIMAL if there is no
> >     declared precision
>     The  default  for  NUMERIC  is  NUMERIC(30,6).  NUMERIC(n) is
>     treated as NUMERIC(n,0). So it is exactly as  Date  says  and
>     since  it  is  already  released,  nothing  to  get changed -
>     period.

I may be misinterpreting Date's synopsis, but I believe that the
default decimal location should be zero, rather than 6. The
"precision" terminology is from SQL92, and refers to the total number
of digits, not the position of the decimal point (as one might
reasonably expect from the usual usage of the word).

Implementation flexibility is allowed in the default total number of
digits, not the default location of the decimal point.

Regards.

                      - Tom

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Tue, 11 May 1999 03:03:23 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] numeric & decimal