From: | Sven Welte <Sven(dot)Welte(at)gmxpro(dot)net> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | strange (numeric) casting behaviour |
Date: | 2006-05-29 02:04:13 |
Message-ID: | 20060529020413.234870@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I'm experience some strange behaviour when casting numeric values.
Given the following SQL-Statement:
SELECT
a_int,
a_num,
CAST (a_num AS NUMERIC(9,1)) AS CastTo9_1,
CAST (a_num AS NUMERIC(9,2)) AS CastTo9_2,
CAST (a_num AS NUMERIC(9,3)) AS CastTo9_3
FROM f_numtest();
Generated output is:
a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | 8.6317245901639344 | 8.632
Expected output was:
a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | --> 8.63 | 8.632
reproducable testcase:
CREATE TABLE numtest (
n NUMERIC(9,2),
i INTEGER
);
INSERT INTO numTest VALUES (13163.38000, 1525);
CREATE TABLE frslt_test (
a_int INTEGER,
a_num NUMERIC(9,2)
);
CREATE OR REPLACE FUNCTION f_numTest() RETURNS SETOF frslt_test AS '
SELECT CAST (SUM(i) AS INTEGER),
SUM(n)/SUM(i)
FROM Numtest
' LANGUAGE 'sql';
SELECT
a_int,
a_num,
CAST (a_num AS NUMERIC(9,1)) AS CastTo9_1,
CAST (a_num AS NUMERIC(9,2)) AS CastTo9_2,
CAST (a_num AS NUMERIC(9,3)) AS CastTo9_3
FROM f_numtest();
select version();
a_int | a_num | castto9_1 | castto9_2 | castto9_3
-------+--------------------+-----------+--------------------+-----------
1525 | 8.6317245901639344 | 8.6 | 8.6317245901639344 | 8.632
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.3 on i486-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu4)
From | Date | Subject | |
---|---|---|---|
Next Message | Henrik Zagerholm | 2006-05-29 13:31:59 | reindexdb command utlility |
Previous Message | Germán Aracil Boned | 2006-05-28 16:45:49 | int64 and spi_getbinval bug !? |