BUG #1587: Unexpected behaviour of numeric datatype when mixed with float4, domains and plpgsql variables - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      1587
Logged by:          Ezequiel Tolnay
Email address:      mail@etolnay.com.ar
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;