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