Un type dédié avec PostgreSQL
Si vous voulez stocker des entiers, nombres décimaux dont la partie décimale est nulle, consultez cet article qui les traite spécifiquement.
Le présent article traite du stockage des décimaux pour le calcul exact. Cet article traite par ailleurs des types dédiés au calcul en virgule flottante.
Le standard SQL et PostgreSQL proposent pour le calcul exact le type NUMERIC (ou DECIMAL). NUMERIC(p, s) permet de stocker un nombre comprenant au total p chiffres DONT s chiffres après la virgule. p signifie precision (précision) et s scale (échelle). s peut être égal à 0 ou positif.
- Si s est égal à 0, le nombre est arrondi à la valeur entière.
- Si s est positif, le nombre est arrondi à s chiffres après la virgule.
Omettre p et s et donc déclarer simplement la colonne en NUMERIC revient à indiquer la précision et l’échelle maximales possibles
Le stockage des NUMERIC par PostgreSQL est dynamique, la taille réelle d’un NUMERIC dépend de la taille du nombre à stocker.
Les opérations effectuées sur les types de données INTEGER ou FLOAT sont plus performantes que sur les données NUMERIC. Mais les performances ne sont pas le premier critère dans le choix d’un type de données. Il faut d’abord penser au besoin fonctionnel. Les données monétaires, par exemple, seront la plupart du temps stockées en NUMERIC. Il existe un type de données dédié, MONEY, mais son utilisation est déconseillée. Les erreurs d’arrondi qui s’accumulent dans les calculs intermédiaires rendent par ailleurs souvent inadaptés l’utilisation des type FLOAT.
Démonstration avec PostgreSQL 10 :
select version();
version
----------------------------------------------------------------------------------------------------------------
PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 ligne)
create table t1(c1 numeric);
CREATE TABLE
insert into t1 values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789);
INSERT 0 1
select c1 from t1;
c1
---------------------------------------------------------------------------------------------
123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789
(1 ligne)
create table t2(c1 numeric(5,2));
CREATE TABLE
insert into t2 values(12345.6);
ERREUR: champ numérique en dehors des limites
DÉTAIL : Un champ de précision 5 et d'échelle 2 doit être arrondi à une valeur
absolue inférieure à 10^3.
insert into t2 values(123.1234);
INSERT 0 1
select c1 from t2;
c1
--------
123.12
(1 ligne)
create table t3(c1 numeric(5,0));
CREATE TABLE
insert into t3 values(123.1234);
INSERT 0 1
select c1 from t3;
c1
-----
123
(1 ligne)
create table t4(c1 numeric(5,-2));
ERREUR: l'échelle NUMERIC -2 doit être comprise entre 0 et 5
LIGNE 1 : create table t4(c1 numeric(5,-2));
Rien à signaler. C’est simple, clair et tout fonctionne exactement comme décrit dans la documentation.
Et si on part d’Oracle ?
Le type de données historique utilisée pour stocker TOUS les nombres sous Oracle Database est le NUMBER même si ce SGBD permet aussi, depuis sa version 10g, d’utiliser de vrais FLOAT, c’est à dire des FLOAT qui ne sont pas un simple sous-type de NUMBER.
La version simple de l’histoire est qu’un NUMBER d’Oracle Database est équivalent à un NUMERIC de PostgreSQL et qu’un NUMBER(p, s) d’Oracle est équivalent à un NUMERIC(p, s) de PostgreSQL. Si vous devez viser la portabilité, la documentation PostgreSQL recommande de toujours fixer explicitement p et s.
Si vous ne le faites pas et que vous utilisez le type NUMERIC pour créer vos schémas sous Oracle et PostgreSQL, il y a tout de même quelques nota bene au niveau portabilité. En effet, si p et s sont omis avec Oracle Database, un NUMERIC devient, d’après la commande DESC de sqlplus, un NUMBER(38). Mais c’est en réalité un NUMBER(p, 0) avec p nul. L’échelle à 0 par défaut peut sembler étrange mais, sur ce point, Oracle ne fait que se conformer au standard SQL, au contraire de PostgreSQL. Le problème vient surtout potentiellement de la précision p nulle qui peut induire en erreur. Vous pourrez entrer de très grands nombres sans avoir d’erreur à l’insertion (pas de vérification de précision) mais cela n’augmente PAS le nombre maximal de chiffres SIGNIFICATIFS du type NUMBER d’Oracle. En pratique rassurez-vous, tout cela n’a aucune importance à part si vous devez stocker de très très très grands nombres avec beaucoup de chiffres significatifs. De toute façon, qui peut le plus peut le moins et vous n’aurez pas de problème dans le sens Oracle Database vers PostgreSQL.
Dernier point à noter, il est possible de définir un NUMBER(p, s) avec s < 0 sous Oracle Database, ce qui est impossible avec NUMERIC sous PostgreSQL. number(p, s) avec s négatif signifie que le nombre est arrondi à la puissance de 10 correspondante.
Démonstration avec Oracle Database 12.1.0.2 :
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
create table t1(c1 numeric);
Table created.
desc t1;
Name Null? Type
----------------------- -------- ----------------
C1 NUMBER(38)
insert into t1 values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789);
1 row created.
set numwidth 50
select * from t1;
C1
-----------------------------------------------
123456789123456789123456789123456789123000000
insert into t1 values(1.1);
1 row created.
insert into t1 values(1.6);
1 row created.
col c1 for 999.999
select * from t1;
C1
--------
########
1.000
2.000
create table t2 (c1 number(38));
Table created.
desc t2;
Name Null? Type
----------------------- -------- ----------------
C1 NUMBER(38)
insert into t2 values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789);
insert into t2 values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
create table t3 (c1 number(39));
create table t3 (c1 number(39))
*
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)
create table t3 (c1 number);
Table created.
insert into t3 values(123456789123456789123456789123456789123456789.123456789123456789123456789123456789123456789);
1 row created.
set numwidth 50
select * from t3;
C1
-----------------------------------------------
123456789123456789123456789123456789123000000
insert into t3 values(1.1);
1 row created.
insert into t3 values(1.6);
1 row created.
col c1 for 999.999
select * from t3;
C1
--------
########
1.100
1.600
set lines 300
col table_name for a10
col COLUMN_NAME for a10
col data_type for a10
select TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_PRECISION, DATA_SCALE from user_tab_columns where column_name = 'C1';
TABLE_NAME COLUMN_NAM DATA_TYPE DATA_PRECISION DATA_SCALE
---------- ---------- ---------- -------------------------------------------------- --------------------------------------------------
T1 C1 NUMBER 0
T2 C1 NUMBER 38 0
T3 C1 NUMBER
create table t4(c1 numeric(5,2), c2 number(5,2));
Table created.
desc t4
Name Null? Type
----------------------- -------- ----------------
C1 NUMBER(5,2)
C2 NUMBER(5,2)
insert into t4 values(123.1234, 123.1234);
1 row created.
insert into t4 values(12345.6, 12345.6);
insert into t4 values(12345.6, 12345.6)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
col c1 for 99999999.999999999
col c2 for 99999999.999999999
select * from t4;
C1 C2
------------------- -------------------
123.120000000 123.120000000
create table t5(c1 numeric(5,0), c2 number(5,0));
Table created.
insert into t5 values(123.1234, 123.1234);
1 row created.
select * from t5;
C1 C2
------------------- -------------------
123.000000000 123.000000000
create table t6(c1 numeric(5,-2), c2 number(5,-2));
create table t6(c1 numeric(5,-2), c2 number(5,-2));
Table created.
insert into t6 values(123.1234, 123.1234);
1 row created.
select * from t6;
C1 C2
------------------- -------------------
100.000000000 100.000000000
Le NUMBER d’Oracle Database est proche du type NUMERIC du standard SQL et de PostgreSQL donc, pour ne pas risquer de mauvaises surprises au niveau fonctionnel, le type NUMERIC pourrait systématiquement être utilisé lors d’une migration.
Cependant, pour les applicatifs Oracle anciens ne tirant pas parti des FLOAT lorsque c’est possible, il peut être intéressant à l’occasion de la migration de s’interroger colonne par colonne si un type de données FLOAT ne suffit pas. De même, si la colonne est uniquement destinée à recevoir des entiers, il est pertinent de passer sur un type dédié.