decimal and numeric types - Mailing list pgsql-hackers

<tt>Hi all,</tt><tt></tt><p><tt>--I'm trying numeric and decimal types and I have a couple of questions...</tt><br
/><tt></tt> <tt></tt><p><tt>CREATETABLE Test (num NUMERIC(7,2), dec DECIMAL(7,2), flt8 FLOAT(15));</tt><br
/><tt>CREATE</tt><br/><tt>INSERT INTO Test VALUES (1,1,1);</tt><br /><tt>INSERT 191083 1</tt><br /><tt>INSERT INTO Test
VALUES(2.343,2.343,2.343);</tt><br /><tt>INSERT 191084 1</tt><br /><tt>INSERT INTO Test VALUES
(-3.0,-3.0,-3.0);</tt><br/><tt>INSERT 191085 1</tt><br /><tt>select * from test;</tt><br /><tt>  num|  dec|
flt8</tt><br/><tt>-----+-----+-----</tt><br /><tt> 1.00|    1|    1</tt><br /><tt> 2.34|2.343|2.343</tt><br
/><tt>-3.00|  -3|   -3</tt><br /><tt>(3 rows)</tt><tt></tt><p><tt>--decimal has the same format of float instead of
numeric.</tt><tt></tt><p><tt>--what'sthe difference between decimal and numeric?</tt><br /><tt>--psql show both of them
asnumeric:</tt><br /><tt>prova=> \d test</tt><br /><tt>Table    = test</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>|             
Field              |              Type                | Length|</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><br/><tt>|
num                             | numeric                          |   var |</tt><br /><tt>|
dec                             | numeric                          |   var |</tt><br /><tt>|
flt8                            | float8                           |     8 |</tt><br
/><tt>+----------------------------------+----------------------------------+-------+</tt><tt></tt><p><tt>SELECT
flt8,CAST(flt8 AS numeric(5,3)), CAST (flt8 AS decimal(5,3)) FROM Test;</tt><br /><tt> flt8|numeric|numeric</tt><br
/><tt>-----+-------+-------</tt><br/><tt>    1|      1|      1</tt><br /><tt>2.343|  2.343|  2.343</tt><br /><tt>  
-3|    -3|     -3</tt><br /><tt>(3 rows)</tt><p>--Seems that CAST translates float to numeric even if I specify
decimal.<br />-- in reality the label says numeric but data has the decimal format instead of numeric. <p>--numeric and
decimaldoesn't support arithmetic operations with floats... <p>SELECT num-flt8, dec-flt8 FROM Test; <br />ERROR: 
Unableto identify an operator '-' for types 'numeric' and 'float8' <br />        You will have to retype this query
usingan explicit cast <br />SELECT num+flt8, dec+flt8 FROM Test; <br />ERROR:  Unable to identify an operator '+' for
types'numeric' and 'float8' <br />        You will have to retype this query using an explicit cast <br />SELECT
num*flt8,dec*flt8 FROM Test; <br />ERROR:  Unable to identify an operator '*' for types 'numeric' and 'float8' <br
/>       You will have to retype this query using an explicit cast <br />SELECT num/flt8, dec/flt8 FROM Test; <br
/>ERROR: Unable to identify an operator '/' for types 'numeric' and 'float8' <br />        You will have to retype this
queryusing an explicit cast <br />SELECT * FROM Test WHERE dec < flt8; <br />ERROR:  Unable to identify an operator
'<'for types 'numeric' and 'float8' <br />        You will have to retype this query using an explicit cast <p>--I
createthis function: <br />create function dec_float8_lt(decimal,float8) returns bool as ' <br />declare <br />       
f1float8; <br />        f2 float8; <br />begin <br />        f1:= $1; <br />        f2:= $2; <br />        return (f1
<f2); <br />end; <br />' language 'plpgsql'; <br />CREATE <p>--and I tried to create this operator.. but CREATE
OPERATORdoesn't recognize decimal/numeric keyword... <p>create operator < ( <br />        leftarg=decimal, <br
/>       rightarg=float8, <br />        procedure=dec_float8_lt <br />        ); <br />ERROR:  parser: parse error at
ornear "decimal" <p>SELECT * FROM Test WHERE dec < flt8; <br />ERROR:  Unable to identify an operator '<' for
types'numeric' and 'float8' <br />        You will have to retype this query using an explicit cast <p>select
dec_float8_lt(1.23,12.2);<br />dec_float8_lt <br />------------- <br />t <br />(1 row) <p>I sent a report about this
topicsome weeks ago but I had no response. <p>José <br />