Thread: Re: [HACKERS] numeric & decimal
> 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) #
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) #
> 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
<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 />
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
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) #
> > > 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
>
> > > > 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) #
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
> > > - 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) #
> > 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