Skip site navigation (1) Skip section navigation (2)

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

From: Ezequiel Tolnay <mail(at)etolnay(dot)com(dot)ar>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Unexpected behaviour of numeric datatype when mixed with,float4, domains and plpgsql variables
Date: 2005-04-07 09:20:25
Message-ID: d32tup$300c$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Responses

pgsql-bugs by date

Next:From: MichaelaDate: 2005-04-07 15:04:35
Subject: BUG #1590: Comparison Operation with Strings
Previous:From: Lionel BoutonDate: 2005-04-07 09:14:30
Subject: BUG #1588: pg_autovacuum sleep parameter overflow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group