decimal and numeric types

Hi all,

--I'm trying numeric and decimal types and I have a couple of
questions...

CREATE TABLE Test (num NUMERIC(7,2), dec DECIMAL(7,2), flt8 FLOAT(15));
CREATE
INSERT INTO Test VALUES (1,1,1);
INSERT 191083 1
INSERT INTO Test VALUES (2.343,2.343,2.343);
INSERT 191084 1
INSERT INTO Test VALUES (-3.0,-3.0,-3.0);
INSERT 191085 1
select * from test;
num| dec| flt8
-----+-----+-----
1.00| 1| 1
2.34|2.343|2.343
-3.00| -3| -3
(3 rows)

--decimal has the same format of float instead of numeric.

--what's the difference between decimal and numeric?
--psql show both of them as numeric:
prova=> \d test
Table = test
+----------------------------------+----------------------------------+-------+

| Field | Type |
Length|
+----------------------------------+----------------------------------+-------+

| num | numeric
| var |
| dec | numeric
| var |
| flt8 | float8
| 8 |
+----------------------------------+----------------------------------+-------+

SELECT flt8,CAST (flt8 AS numeric(5,3)), CAST (flt8 AS decimal(5,3))
FROM Test;
flt8|numeric|numeric
-----+-------+-------
1| 1| 1
2.343| 2.343| 2.343
-3| -3| -3
(3 rows)

--Seems that CAST translates float to numeric even if I specify decimal.

-- in reality the label says numeric but data has the decimal format
instead of numeric.

--numeric and decimal doesn't support arithmetic operations with
floats...

SELECT num-flt8, dec-flt8 FROM Test;
ERROR: Unable to identify an operator '-' for types 'numeric' and
'float8'
You will have to retype this query using an explicit cast
SELECT num+flt8, dec+flt8 FROM Test;
ERROR: Unable to identify an operator '+' for types 'numeric' and
'float8'
You will have to retype this query using an explicit cast
SELECT num*flt8, dec*flt8 FROM Test;
ERROR: Unable to identify an operator '*' for types 'numeric' and
'float8'
You will have to retype this query using an explicit cast
SELECT num/flt8, dec/flt8 FROM Test;
ERROR: Unable to identify an operator '/' for types 'numeric' and
'float8'
You will have to retype this query using an explicit cast
SELECT * FROM Test WHERE dec < flt8;
ERROR: Unable to identify an operator '<' for types 'numeric' and
'float8'
You will have to retype this query using an explicit cast

--I create this function:
create function dec_float8_lt(decimal,float8) returns bool as '
declare
f1 float8;
f2 float8;
begin
f1:= $1;
f2:= $2;
return (f1 < f2);
end;
' language 'plpgsql';
CREATE

--and I tried to create this operator.. but CREATE OPERATOR doesn't
recognize decimal/numeric keyword...

create operator < (
leftarg=decimal,
rightarg=float8,
procedure=dec_float8_lt
);
ERROR: parser: parse error at or near "decimal"

SELECT * FROM Test WHERE dec < flt8;
ERROR: Unable to identify an operator '<' for types 'numeric' and
'float8'
You will have to retype this query using an explicit cast

select dec_float8_lt(1.23,12.2);
dec_float8_lt
-------------
t
(1 row)

I sent a report about this topic some weeks ago but I had no response.

José