select version();
version
--------------------------------------------------------------------------------------------------
PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18) 6.3.0 20170516, 64-bit
(1 ligne)
create table t_real(c1 real);
CREATE TABLE
create table t_double (c1 double precision);
CREATE TABLE
create table t_numeric (c1 numeric);
CREATE TABLE
insert into t_real values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789);
ERREUR: valeur en dehors des limites : dépassement
insert into t_double values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789);
INSERT 0 1
insert into t_numeric values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789);
INSERT 0 1
delete from t_double;
DELETE 1
delete from t_numeric;
DELETE 1
insert into t_real values(1);
INSERT 0 1
update t_real set c1 = c1/9999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
ERREUR: valeur en dehors des limites : trop petit
insert into t_double values(1);
INSERT 0 1
update t_double set c1 = c1/9999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
UPDATE 1
insert into t_numeric values(1);
INSERT 0 1
update t_numeric set c1 = c1/9999999999999999999999999999999999999999999999999999999999999999999999999999999999999;
UPDATE 1
delete from t_real;
DELETE 1
delete from t_double;
DELETE 1
delete from t_numeric;
DELETE 1
insert into t_real(c1) values(987654321.337);
INSERT 0 1
select
(c1/7*8/13*7/11*12/3*19/23*14/17*20/37*60/41*79/83/101*117*4/5*5/3*2/17*19/100*89/1000*c1/2*3/17*19*7/5*11/13)::bigint
from t_real;
int8
-----------------
682298862604604
(1 ligne)
insert into t_double(c1) values(987654321.337);
INSERT 0 1
select
(c1/7*8/13*7/11*12/3*19/23*14/17*20/37*60/41*79/83/101*117*4/5*5/3*2/17*19/100*89/1000*c1/2*3/17*19*7/5*11/13)::bigint
from t_double;
int8
-----------------
682298842345394
(1 ligne)
insert into t_numeric(c1) values(987654321.337);
INSERT 0 1
select
(c1/7*8/13*7/11*12/3*19/23*14/17*20/37*60/41*79/83/101*117*4/5*5/3*2/17*19/100*89/1000*c1/2*3/17*19*7/5*11/13)::bigint
from t_numeric;
int8
-----------------
682298842345395
(1 ligne)
insert into t_real select * from generate_series(1,1000000);
INSERT 0 1000000
insert into t_double select * from generate_series(1,1000000);
INSERT 0 1000000
insert into t_numeric select * from generate_series(1,1000000);
INSERT 0 1000000
\timing
select avg(c1/3) from t_real;
avg
------------------
166995.884449449
(1 ligne)
Temps : 99,731 ms
...
Temps : 107,665 ms
...
Temps : 99,100 ms
select avg(c1/3) from t_double;
avg
------------------
166995.884444561
(1 ligne)
Temps : 113,804 ms
...
Temps : 108,524 ms
...
Temps : 108,479 ms
select avg(c1/3) from t_numeric;
avg
-----------------------------
166995.88444456122210211090
(1 ligne)
Temps : 244,740 ms
...
Temps : 213,950 ms
...
Temps : 232,309 ms
Les types REAL et DOUBLE PRECISION ont les avantages et inconvénients attendus. Le gain de performance est impressionnant. Dans notre exemple, la durée dexécution lors du dernier test est divisée par 2.