The following bug has been logged online:
Bug reference: 1587
Logged by: Ezequiel Tolnay
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system: Windows XP Advanced Server
Description: Unexpected behaviour of numeric datatype when mixed with
float4, domains and plpgsql variables
Details:
Tables with columns specified as numeric through the use of a domain are
capable of containing values with a higher precision than allowed.
The following snippet can be used to reproduce the error (run each step
sequencially and observe results):
--step 1: direct execution on base type
SELECT 92::numeric(15,4) * 0.2::float4;
--step 2: using a function variable and a domain
CREATE DOMAIN currency AS numeric(15,4);
CREATE TABLE test (id serial, amt currency);
CREATE FUNCTION f_test(currency) RETURNS currency AS $$
DECLARE n currency;
BEGIN n := $1 * 0.2::float4;
INSERT INTO test (amt) VALUES (n); RETURN n;
END $$ LANGUAGE PLPGSQL;
SELECT f_test(92);
--step 3: wrong precision stored in the table!
SELECT id, amt, amt::currency as amt_still_bad,
amt::numeric(15,4) as amt_casted_as_base,
amt::float::currency as amt_casted_back_n_forth
FROM test;
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
