Thread: strange (numeric) casting behaviour

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)

Sven Welte <Sven.Welte@gmxpro.net> writes:
> I'm experience some strange behaviour when casting numeric values.

This doesn't really have anything to do with casting as such.  The
system thinks it can throw away the cast to NUMERIC(9,2) because the
function result is declared as already NUMERIC(9,2) ... but the function
is not actually constraining its result that way.  In general a function
returning NUMERIC is considered to return unconstrained NUMERIC (and
likewise for VARCHAR etc).

Possibly we should disallow the length decoration on columns used in a
function result typedef, because it fools people into thinking that such
a decoration will be enforced.

            regards, tom lane