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ï¿½



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é
Mon, 26 Apr 1999 10:59:58 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
decimal and numeric types