PostgreSQLLa base de données la plus sophistiquée au monde.

8. Types de données

PostgreSQL™ offre un large choix de types de données disponibles nativement. Les utilisateurs peuvent ajouter de nouveaux types à PostgreSQL™ en utilisant la commande CREATE TYPE.

Le Tableau 8.1, « Types de données » montre tous les types de données généraux disponibles nativement. La plupart des types de données alternatifs listés dans la colonne « Alias » sont les noms utilisés en interne par PostgreSQL™ pour des raisons historiques. Il existe également d'autres types de données internes ou obsolètes, mais ils ne sont pas listés ici.

Tableau 8.1. Types de données

Nom Alias Description
bigint int8 Entier signé sur 8 octets
bigserial serial8 Entier sur 8 octets à incrémentation automatique
bit [ (n) ]   Suite de bits de longueur fixe
bit varying [ (n) ] varbit Suite de bits de longueur variable
boolean bool Booléen (Vrai/Faux)
box   Boîte rectangulaire dans le plan
bytea   Donnée binaire (« tableau d'octets »)
character varying [ (n) ] varchar [ (n) ] Chaîne de caractères de longueur variable
character [ (n) ] char [ (n) ] Chaîne de caractères de longueur fixe
cidr   Adresse réseau IPv4 ou IPv6
circle   Cercle dans le plan
date   Date du calendrier (année, mois, jour)
double precision float8 Nombre à virgule flottante de double précision
inet   Adresse d'ordinateur IPv4 ou IPv6
integer int, int4 Entier signé sur 4 octets
interval [ (p) ]   Intervalle de temps
line   Droite (infinie) dans le plan
lseg   Segment de droite dans le plan
macaddr   Adresse MAC
money   Montant monétaire
numeric [ (p, s) ] decimal [ (p, s) ] Nombre exact dont la précision peut être précisée
path   Chemin géométrique dans le plan
point   Point géométrique dans le plan
polygon   Chemin géométrique fermé dans le plan
real float4 Nombre à virgule flottante de simple précision
smallint int2 Entier signé sur 2 octets
serial serial4 Entier sur 4 octets à incrémentation automatique
text   Chaîne de caractères de longueur variable
time [ (p) ] [ without time zone ]   Heure du jour
time [ (p) ] with time zone timetz Heure du jour, avec fuseau horaire
timestamp [ (p) ] [ without time zone ]   Date et heure
timestamp [ (p) with time zone timestamptz Date et heure, avec fuseau horaire

[Note]

Compatibilité

Les types suivants sont conformes à la norme SQL: bit, bit varying, boolean, char, character varying, character, varchar, date, double precision, integer, interval, numeric, decimal, real, smallint, time (avec et sans fuseau horaire), timestamp (avec et sans fuseau horaire).

Chaque type de données a une représentation externe déterminée par ses fonctions d'entrée et de sortie. De nombreux types de données internes ont un format externe évident. Cependant, certains types sont spécifiques à PostgreSQL™, comme les chemins géométriques, ou acceptent différents formats, comme les types de données de date et d'heure. Certaines fonctions d'entrée et de sortie ne sont pas inversables : le résultat de la fonction de sortie peut manquer de précision comparé à l'entrée initiale.

8.1. Types numériques

Les types numériques sont constitués d'entiers de 2, 4 ou 8 octets, de nombres à virgule flottante de 4 ou 8 octets et de décimaux dont la précision peut être indiquée. Le Tableau 8.2, « Types numériques » précise les types disponibles.

Tableau 8.2. Types numériques

Nom Taille de stockage Description Étendue
smallint 2 octets entier de faible étendue de -32768 à +32767
integer 4 octets entier habituel de -2147483648 à +2147483647
bigint 8 octets grand entier de -9223372036854775808 à 9223372036854775807
decimal variable précision indiquée par l'utilisateur, valeur exacte pas de limite
numeric variable précision indiquée par l'utilisateur, valeur exacte pas de limite
real 4 octets précision variable, valeur inexacte précision de 6 décimales
double precision 8 octets précision variable, valeur inexacte précision de 15 décimales
serial 4 octets entier à incrémentation automatique de 1 à 2147483647
bigserial 8 octets entier de grande taille à incrémentation automatique de 1 à 9223372036854775807

La syntaxe des constantes pour les types numériques est décrite dans la Section 4.1.2, « Constantes ». Les types numériques ont un ensemble complet d'opérateurs arithmétiques et de fonctions. On peut se référer au Chapitre 9, Fonctions et opérateurs pour plus d'informations. Les sections suivantes décrivent ces types en détail.

8.1.1. Types entiers

Les types smallint, integer et bigint stockent des nombres entiers, c'est-à-dire sans décimale, de différentes étendues. Toute tentative d'y stocker une valeur en dehors de l'échelle produit une erreur.

Le type integer est le plus courant. Il offre un bon compromis entre capacité, espace utilisé et performance. Le type smallint n'est utilisé que si l'économie d'espace disque est le premier critère de choix. Le type bigint ne doit être utilisé que si le type integer n'offre pas une étendue suffisante car le type integer est nettement plus rapide.

Le type bigint peut ne pas fonctionner correctement sur toutes les plates-formes, car il repose sur la capacité du compilateur à supporter les entiers de 8 octets. Sur une machine qui ne les supporte pas, bigint se comporte comme integer (mais prend bien huit octets d'espace de stockage). Cela dit, les auteurs n'ont pas connaissance de plate-forme raisonnable sur laquelle il en va ainsi.

SQL ne définit que les types de données integer (ou int) et smallint. Le type bigint et les noms de types int2, int4, et int8 sont des extensions, partagées par d'autres systèmes de bases de données SQL.

8.1.2. Nombres à précision arbitraire

Le type numeric peut stocker des nombres contenant jusqu'à 1000 chiffres significatifs et effectuer des calculs exacts. Il est spécialement recommandé pour stocker les montants financiers et autres quantités pour lesquelles l'exactitude est indispensable. Néanmoins, l'arithmétique sur les valeurs numeric est très lente comparée aux types entiers ou aux types à virgule flottante décrits dans la section suivante.

Dans ce qui suit, on utilise les termes suivants : l'échelle d'un numeric est le nombre de chiffres décimaux de la partie fractionnaire, à droite du séparateur de décimales. La précision d'un numeric est le nombre total de chiffres significatifs dans le nombre complet, c'est-à-dire le nombre de chiffres de part et d'autre du séparateur. Donc, le nombre 23.5141 a une précision de 6 et une échelle de 4. On peut considérer que les entiers ont une échelle de 0.

La précision maximale et l'échelle maximale d'une colonne numeric peuvent être toutes deux réglées. Pour déclarer une colonne de type numérique, il faut utiliser la syntaxe :

NUMERIC(précision, échelle)

La précision doit être strictement positive, l'échelle positive ou NULL. Alternativement,

NUMERIC(précision)

indique une échelle de 0.

NUMERIC

sans précision ni échelle crée une colonne dans laquelle on peut stocker des valeurs de n'importe quelle précision ou échelle, dans la limite de la précision implantée. Une colonne de ce type n'impose aucune précision à la valeur entrée, alors que les colonnes numeric ayant une échelle forcent les valeurs entrées à cette échelle. (Le standard SQL demande une précision par défaut de 0, c'est-à-dire de forcer la transformation en entiers. Les auteurs trouvent cela inutile. Dans un souci de portabilité, il est préférable de toujours indiquer explicitement la précision et l'échelle.)

Si l'échelle d'une valeur à stocker est supérieure à celle de la colonne, le système arrondit la valeur au nombre de décimales indiqué pour la colonne. Si le nombre de chiffres à gauche du point décimal est supérieur à la différence entre la précision déclarée et l'échelle déclarée, une erreur est levée.

Les valeurs numériques sont stockées physiquement sans zéro avant ou après. Du coup, la précision déclarée et l'échelle de la colonne sont des valeurs maximales, pas des allocations fixes (en ce sens, le type numérique est plus proche de varchar(n) que de char(n)). Le besoin pour le stockage réel est de deux octets pour chaque groupe de quatre chiffres décimaux, plus huit octets d'en-tête.

En plus des valeurs numériques ordinaires, le type numeric autorise la valeur spéciale NaN qui signifie « not-a-number » (NdT : pas un nombre). Toute opération sur NaN retourne NaN. Pour écrire cette valeur comme une constante dans une requête SQL, elle doit être placée entre guillemets. Par exemple, UPDATE table SET x = 'NaN'. En saisie, la chaîne NaN est reconnue quelque soit la casse utilisée.

Les types decimal et numeric sont équivalents. Les deux types sont dans le standard SQL.

8.1.3. Types à virgule flottante

Les types de données real et double precision sont des types numériques inexacts de précision variable. En pratique, ils sont généralement conformes à la norme IEEE 754 pour l'arithmétique binaire à virgule flottante (respectivement simple et double précision), dans la mesure où les processeurs, le système d'exploitation et le compilateur les supportent.

Inexact signifie que certaines valeurs ne peuvent être converties exactement dans le format interne. Elles sont, de ce fait, stockées sous une forme approchée. Ainsi, stocker puis réafficher ces valeurs peut faire apparaître de légers écarts. Prendre en compte ces erreurs et la façon dont elles se propagent au cours des calculs est le sujet d'une branche entière des mathématiques et de l'informatique, qui n'est pas le sujet de ce document, à l'exception des points suivants :

  • pour un stockage et des calculs exacts, comme pour les valeurs monétaires, le type numeric doit être privilégié ;

  • pour des calculs compliqués avec ces types pour quoi que ce soit d'important, et particulièrement pour le comportement aux limites (infini, zéro), l'implantation spécifique à la plate-forme doit être étudié avec soin ;

  • tester l'égalité de deux valeurs à virgule flottante peut ne pas donner le résultat attendu.

Sur la plupart des plates-formes, le type real a une étendue d'au moins 1E-37 à 1E37 avec une précision d'au moins 6 chiffres décimaux. Le type double precision a généralement une étendue de 1E-307 à 1E+308 avec une précision d'au moins 15 chiffres. Les valeurs trop grandes ou trop petites produisent une erreur. Un arrondi peut avoir lieu si la précision d'un nombre en entrée est trop grande. Les nombres trop proches de zéro qui ne peuvent être représentés autrement que par zéro produisent une erreur (underflow).

En plus des valeurs numériques ordinaires, les types à virgule flottante ont plusieurs valeurs spéciales :

Infinity
-Infinity
NaN

Elles représentent les valeurs spéciales de l'IEEE 754, respectivement « infinity » (NdT : infini), « negative infinity » (NdT : infini négatif) et « not-a-number » (NdT : pas un nombre) (sur une machine dont l'arithmétique à virgule flottante ne suit pas l'IEEE 754, ces valeurs ne fonctionnent probablement pas comme espéré). Lorsqu'elles sont saisies en tant que constantes dans une commande SQL, ces valeurs doivent être placées entre guillemets. Par exemple, UPDATE table SET x = 'Infinity'. En entrée, ces valeurs sont reconnues quelque soit la casse utilisée.

PostgreSQL™ autorise aussi la notation float du standard SQL, ainsi que float(p) pour indiquer des types numériques inexacts. p indique la précision minimale acceptable en chiffres binaires. PostgreSQL™ accepte de float(1) à float(24), qu'il transforme en type real, et de float(25) à float(53), qu'il transforme en type double precision. Toute valeur de p hors de la zone des valeurs possibles produit une erreur. float sans précision est compris comme double precision.

[Note]

Note

Avant PostgreSQL™ 7.4, la précision d'un float(p) était supposée indiquer une précision en chiffres décimaux. Cela a été corrigé pour respecter le standard SQL, qui indique que la précision est indiquée en chiffres binaires. L'affirmation que les real et les double precision ont exactement 24 et 53 bits dans la mantisse est correcte pour les implémentations des nombres à virgule flottante respectant le standard IEEE. Sur les plates-formes non-IEEE, c'est peut-être un peu sous-estimé mais, pour plus de simplicité, la gamme de valeurs pour p est utilisée sur toutes les plates-formes.

8.1.4. Types seriés

Les types de données serial et bigserial ne sont pas de vrais types, mais plutôt un raccourci de notation pour créer des colonnes d'identifiants uniques (similaires à la propriété AUTO_INCREMENT utilisée par d'autres SGBD). Dans la version actuelle, indiquer

CREATE TABLE nom_de_table (
    nom_de_colonne SERIAL
);

est équivalent à écrire :

CREATE SEQUENCE nom_de_table_nom_de_colonne_seq;
CREATE TABLE nom_de_table (
    nom_de_colonne integer NOT NULL DEFAULT nextval('nom_de_table_nom_de_colonne_seq') NOT NULL
);
ALTER SEQUENCE nom_de_table_nom_de_colonne_seq OWNED BY nom_de_table.nom_de_colonne;

Une colonne d'entiers a ainsi été créée dont la valeur par défaut est assignée par un générateur de séquence. Une contrainte NOT NULL est ajoutée pour s'assurer qu'une valeur NULL ne puisse pas être explicitement insérée. (Dans la plupart des cas, une contrainte UNIQUE ou PRIMARY KEY peut être ajoutée pour interdire que des doublons soient créés par accident, mais ce n'est pas automatique.) Enfin, la séquence est marquée « owned by » (possédée par) la colonne pour qu'elle soit supprimée si la colonne ou la table est supprimée.

[Note]

Note

Avant PostgreSQL™ 7.3, serial sous-entendait UNIQUE. Ce n'est plus automatique. Pour qu'une colonne de type serial soit unique ou soit une clé primaire, il faut le préciser, comme pour les autres types.

Pour insérer la valeur suivante de la séquence dans la colonne serial, il faut préciser que la valeur par défaut de la colonne doit être utilisée. Cela peut se faire de deux façons : soit en excluant cette colonne de la liste des colonnes de la commande INSERT soit en utilisant le mot clé DEFAULT.

Les types serial et serial4 sont identiques : ils créent tous les deux des colonnes integer. Les types bigserial et serial8 fonctionnent de la même façon mais créent des colonnes bigint. bigserial doit être utilisé si plus de 231 identifiants sont prévus sur la durée de vie de la table.

La séquence créée pour une colonne serial est automatiquement supprimée quand la colonne correspondante est supprimée. La séquence peut être détruite sans supprimer la colonne, mais la valeur par défaut de la colonne est alors également supprimée.