Skip site navigation (1) Skip section navigation (2)

decimal and numeric types

From: José Soares <jose(at)sferacarta(dot)com>
To: hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: decimal and numeric types
Date: 1999-04-26 08:59:58
Message-ID: 37242B0E.B1907B04@sferacarta.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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é

pgsql-hackers by date

Next:From: The Hermit HackerDate: 1999-04-26 13:39:17
Subject: Re: [HACKERS] regression output
Previous:From: Oleg BartunovDate: 1999-04-26 07:45:02
Subject: Re: [INTERFACES] CASE tools? (slightly off-topic)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group