Обсуждение: Re: [HACKERS] numeric & decimal

Поиск
Список
Период
Сортировка

Re: [HACKERS] numeric & decimal

От
jwieck@debis.com (Jan Wieck)
Дата:
> 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) #

Re: [HACKERS] numeric & decimal

От
jwieck@debis.com (Jan Wieck)
Дата:
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) #

Re: [HACKERS] numeric & decimal

От
Bruce Momjian
Дата:
>     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,
Pennsylvania19026
 


Re: [HACKERS] numeric & decimal

От
José Soares
Дата:
<tt>Jan Wieck ha scritto:</tt><blockquote type="CITE"><tt>> Hi all,</tt><br /><tt>></tt><br /><tt>> I'm trying
numeric& decimal types in v6.5beta1 and I have two questions</tt><br /><tt>> about it.</tt><br
/><tt>></tt><br/><tt>> [...]</tt><br /><tt>></tt><br /><tt>> Second question:</tt><br /><tt>>     Why
PostgreSQLallows to insert 14 digits into a numeric(5,1) ?</tt><br /><tt>></tt><br /><tt>> create table
test(</tt><br/><tt>>         n numeric(10,3),</tt><br /><tt>>         d decimal(5,1)</tt><br /><tt>>
);</tt><tt></tt><p><tt>   For  some reason (dunno why) the parser ignores the precision</tt><br /><tt>    for DECIMAL. 
atttypmodis set hardcoded to -1.  So the above</tt><br /><tt>    is identical to a</tt><tt></tt><p><tt>        CREATE
TABLEtest (n numeric(10,3), d decimal);</tt><tt></tt><p><tt>    I'll  test  what  happens  if I enable it in gram.y and
ifit</tt><br /><tt>    doesn't break any regression commit the changes.</tt><tt></tt><p><tt>Jan</tt><br
/><tt></tt> </blockquote>Great! <br />I have other questions about NUMERICs: <blockquote type="CITE"><tt>create table
test(</tt><br/><tt>        num0 numeric,</tt><br /><tt>        num1 numeric(1),</tt><br /><tt>        num4
numeric(4,1)</tt><br/><tt>);</tt><br /><tt>CREATE</tt><br /><tt>insert into test values
(11111111,11111111,-9,9,-999.99,-999.99);</tt><br/><tt>INSERT 78190 1</tt><br /><tt>select * from test;</tt><br
/><tt>          num0|num1|   num4</tt><br /><tt>---------------+----+-------</tt><br /><tt>11111111.000000|  
9|-1000.0</tt><br/>                  ^^^^^^            ^^^^^^^</blockquote> - I don't understand this default: <br /> 
NUMERIC without size is interpreted as NUMERIC(x,6). Why ? <br />  Standard SQL92 says that NUMERIC without size is
equivalentto NUMERIC(1) <p>- NUMERIC(4,1)  transalte value -999.99 as -1000.0 (greater than his size) <p>Comments?
<p>José<br />  

Re: [HACKERS] numeric & decimal

От
Bruce Momjian
Дата:
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,
Pennsylvania19026
 


Re: [HACKERS] numeric & decimal

От
jwieck@debis.com (Jan Wieck)
Дата:
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) #

Re: [HACKERS] numeric & decimal

От
Thomas Lockhart
Дата:
> > > 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
declaredprecision ..."
 

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


Re: [HACKERS] numeric & decimal

От
jwieck@debis.com (Jan Wieck)
Дата:
>
> > > > 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) #

Re: [HACKERS] numeric & decimal

От
Bruce Momjian
Дата:
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,
Pennsylvania19026
 


Re: [HACKERS] numeric & decimal

От
jwieck@debis.com (Jan Wieck)
Дата:
> > > - 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) #

Re: [HACKERS] numeric & decimal

От
Thomas Lockhart
Дата:
> >   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