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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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é
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 1999-04-26 13:39:17 | Re: [HACKERS] regression output |
Previous Message | Oleg Bartunov | 1999-04-26 07:45:02 | Re: [INTERFACES] CASE tools? (slightly off-topic) |