Thread: Unexpected behaviour of numeric datatype when mixed with,float4, domains and plpgsql variables

I found an odd behaviour, which I believe it is a bug, and for us it is
a serious matter. Can anyone on a platform or version other than
PostgreSQL 8.0.1 on Windows XP Advanced Server check if you can
reproduce the finding on your server?

Tables with columns specified as numeric using a domain type seem to be
capable of containing values with a higher precision than specified,
when mixing in operations with float4 (which we use to store percentages).

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;

Thanks folks.

Ezequiel Tolnay
Good Business Technology
Sydney, NSW, Australia

Ezequiel Tolnay <mail@etolnay.com.ar> writes:
> 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;

plpgsql doesn't currently enforce domain constraints, so the assignment
to n isn't doing the rounding that you expect.  Until someone gets
around to fixing that, an explicit coercion is probably what you need:

    n := cast($1 * 0.2::float4 AS currency);

Keep in mind also that declaring a function result value as a domain
is pretty dangerous, because none of the PLs enforce domain constraints
on their results.

            regards, tom lane

Tom Lane wrote:

>Ezequiel Tolnay <mail@etolnay.com.ar> writes:
>
>
>>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;
>>
>>
>
>plpgsql doesn't currently enforce domain constraints, so the assignment
>to n isn't doing the rounding that you expect.  Until someone gets
>around to fixing that, an explicit coercion is probably what you need:
>
>    n := cast($1 * 0.2::float4 AS currency);
>
>
>
Please note that the critical issue is a column defined as numeric(15,4)
(through the domain alias), but still capable of containing numbers with
higher precision. I wonder how is it possible to store a numeric with
higher precision on a column where the precision is clearly defined. Are
the numeric values stored on a column always variable precision, and the
precision being only enforced on assignment? Wouldn't this impact
heavily in performance and disk space usage?
In any case, it looks like the only flaw is when inserting or updating
with a plpgsql variable into a column defined as a domain for a fixed
precision numeric.