Chapitre 8. Types de données

Table des matières
8.1. Types numériques
8.1.1. Types entiers
8.1.2. Nombre à précision arbitraire
8.1.3. Types à virgule flottante
8.1.4. Types Série
8.2. Types monétaires
8.3. Types caractères
8.4. Types de données binaires
8.5. Types date/heure
8.5.1. Entrée des dates et heures
8.5.2. Affichage des Date/Heure
8.5.3. Fuseaux horaires
8.5.4. Types internes
8.6. Type Boolean
8.7. Types géométriques
8.7.1. Points
8.7.2. Segments de droites
8.7.3. Boxes
8.7.4. Chemins
8.7.5. Polygones
8.7.6. Cercles
8.8. Types d'adresses réseau
8.8.1. inet
8.8.2. cidr
8.8.3. Comparaison de inet et cidr
8.8.4. macaddr
8.9. Types champs de bits
8.10. Tableaux
8.10.1. Déclaration des types de tableaux
8.10.2. Saisie de valeurs de type tableau
8.10.3. Accès aux tableaux
8.10.4. Modification de tableaux
8.10.5. Recherche dans des tableaux
8.10.6. Syntaxe d'entrée et de sortie des tableaux
8.11. Types composites
8.11.1. Déclaration de types composite
8.11.2. Entrée d'une valeur composite
8.11.3. Accéder aux types composite
8.11.4. Modifier les types composite
8.11.5. Syntaxe en entrée et sortie d'un type composite
8.12. Types identifiants d'objets
8.13. Pseudo-Types

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

Tableau 8-1 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. De plus, certains types de données internes ou obsolètes sont disponibles, mais ils ne sont pas listés ici.

Tableau 8-1. Types de données

NomAliasDescription
bigintint8Entier signé de 8 octets
bigserialserial8Entier de 8 octets à incrémentation automatique
bit [ (n) ] Suite de bits de longueur fixe
bit varying [ (n) ]varbitSuite de bits de longueur variable
booleanboolBooléen (Vrai/Faux)
box Boîte rectangulaire dans le plan
bytea Donnée binaire (<< tableau d'octets >>)
character varying [ (n) ]varchar [ (n) ]Suite de caractères de longueur variable
character [ (n) ]char [ (n) ]Suite 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 precisionfloat8Nombre à virgule flottante de double précision
inet Adresse d'ordinateur IPv4 ou IPv6
integerint, int4Entier signé de 4 octets
interval [ (p) ] Intervalle de temps
line Ligne infinie dans le plan
lseg Segment de droite dans le plan
macaddr adresse MAC
money montant d'une devise
numeric [ (p, s) ]decimal [ (p, s) ]Nombre exact de la précision indiqué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
realfloat4Nombre à virgule flottante de simple précision
smallintint2Entier signé de 2 octets
serialserial4Entier de 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 zonetimetzHeure du jour, avec fuseau horaire
timestamp [ (p) ] [ without time zone ] Date et heure
timestamp [ (p) with time zonetimestamptzDate et heure, avec fuseau horaire

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 type de données internes ont un format externe évident. Cependant, certains types sont soit spécifiques à PostgreSQL, comme les chemins géométriques, ou ont différents formats possibles, 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 perdre de la 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 nombre à virgule flottante de 4 ou 8 octets, et de décimaux à précision sélectionnable.

Tableau 8-2. Types numériques

NomTaille de stockageDescriptionÉtendue
smallint2 octetsentier de faible étendue-32768 à +32767
integer4 octetsentiers les plus courants-2147483648 à +2147483647
bigint8 octetsgrands entiers-9223372036854775808 à 9223372036854775807
decimalvariablePrécision indiquée par l'utilisateur. Valeurs exactespas de limite
numericvariablePrécision indiquée par l'utilisateur. Valeurs exactespas de limite
real4 octetsPrécision variable. Valeurs inexactesprécision de 6 décimales
double precision8 octetsPrécision variable. Valeurs inexactesprécision de 15 décimales
serial4 octetsentier à incrémentation automatique1 à 2147483647
bigserial8 octetsentier de grande taille à incrémentation automatique1 à 9223372036854775807

La syntaxe des constantes pour les types numériques est décrite dans Section 4.1.2. Les types numériques ont un ensemble complet d'opérateurs arithmétiques et de fonctions. Référez vous à Chapitre 9 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 trop grande ou trop petite 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). Ceci dit, nous ne connaissons pas de plate-forme raisonnable sur laquelle il en va ainsi.

SQL ne spécifie 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, qui sont partagées avec d'autres systèmes de bases de données SQL.

8.1.2. Nombre à précision arbitraire

Le type numeric peut stocker des nombres avec jusqu'à 1000 chiffres significatifs et effectuer des calculs exacts. Il est spécialement recommander 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 lent comparé aux types entiers ou au type à 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. La précision d'un numeric est le nombre total de chiffres significatifs dans le nombre entier, c'est à dire avant et après la virgule. 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 maximum et l'échelle maximum d'une colonne numeric peuvent être réglés. Pour déclarer une colonne de type numérique, il faut utiliser la syntaxe.

NUMERIC(précision, échelle)

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

NUMERIC(précision)

indique une échelle de 0. Préciser

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, jusqu'à la limite de précision. Une colonne de ce type ne forcera aucune valeur entrée à une précision particulière, alors que les colonnes numeric avec 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. Nous trouvons ça inutile. Si vous êtes soucieux de portabilité, précisez toujours la précision et l'échelle explicitement.)

Si l'échelle d'une valeur est supérieure à l'échelle d'une colonne, le système tentera d'arrondir la valeur au nombre de chiffres après la virgule spécifiée. Ensuite, si le nombre de chiffres à gauche du point décimal dépasse la précision déclarée moins 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 maximums, pas des allocations fixes. (En ce sens, le type numérique est plus proche de varchar(n) que de char(n).)

En plus des valeurs numériques ordinaires, le type numeric autorise la valeur spéciale NaN, signifiant << not-a-number >> (NdT : pas un nombre). Toute opération sur NaN renvoie un autre NaN. En écrivant cette valeur comme une constante dans une requête SQL, vous devez placer des guillemets autour. 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 à précision variable inexacts. 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, et sont stockées sous forme d'approximations, si bien que 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. Nous n'en dirons pas plus que ce qui suit:

  • Si vous avez besoin d'un stockage et de calculs exacts, comme pour les valeurs monétaires, utilisez plutôt le type numeric.

  • Si vous voulez faire des calculs compliqués avec ces types pour quoi que ce soit d'important, et particulièrement si vous comptez sur certains comportements aux limites (infinis, zéro), alors vous devriez étudier le comportement de votre plate-forme avec soin.

  • Tester l'égalité de deux valeurs à virgule flottante peux 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 fonctionneront probablement pas comme c'est attendu.) Lors de la saisie de ces valeurs en tant que constantes dans une commande SQL, vous devez placer des guillemets autour. 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. Ici, p indique la précision minimale acceptable en chiffres binaires. PostgreSQL accepte float(1) à float(24), transformés en type real et float(25) to float(53), transformés en type double precision. Toute valeur de p hors de la zone de valeurs possible produit une erreur. float sans précision est compris comme double precision.

Note : Avant PostgreSQL 7.4, la précision d'un float(p) était supposée indiquer une précision en chiffres décimaux. Nous l'avons corrigée 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 Série

Les types de données serial et bigserial ne sont pas de vrais types, mais plutôt un raccourci de notation pour décrire 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_colname_seq;
CREATE TABLE nom_de_table (
    nom_de_colonne integer DEFAULT nextval('nom_de_table_nom_de_colonne_seq') NOT NULL
);

Ainsi, nous avons créé une colonne d'entiers et fait en sorte que ses valeurs par défaut soient assignées 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, vous voudrez aussi ajouter une contrainte UNIQUE ou PRIMARY KEY pour interdire que des doublons soient créés par accident, mais ce n'est pas automatique.

Note : Avant PostgreSQL 7.3, serial sous entendait UNIQUE. Ce n'est plus automatique. Si vous souhaitez qu'une colonne serial soit unique ou soit une clé primaire, il faut le préciser, comme pour un autre type.

Pour insérer la valeur suivante de la séquence dans la colonne serial, il faut faire en sorte d'utiliser la valeur par défaut de la colonne. Cela peut se faire de deux façons: soit en excluant cette colonne de la liste des colonnes de la commande INSERT, ou 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, et créent des colonnes bigint. bigserial doit être utilisé si vous pensez utiliser plus de 231 identifiants dans toute la vie de la table.

La séquence créée pour une colonne serial est automatiquement supprimée quand la colonne correspondante est supprimée, et ne peut l'être autrement. (Ce n'était pas le cas avant la version 7.3 de PostgreSQL. Notez que ce lien de suppression automatique de séquence ne fonctionnera pas pour une base restaurée d'une sauvegarde SQL (dump) antérieure à la version 7.3. La sauvegarde ne contient en effet pas l'information nécessaire à l'établissement du lien de dépendance.) De plus, ce lien de dépendance n'est mis que pour la colonne de type serial elle même. Si d'autres colonnes référencent la séquence (par exemple en appellent la fonction nextval), elles ne peuvent plus fonctionner si la séquence est supprimée. Utiliser le type serial de cette façon n'est pas recommandé. Si vous souhaitez remplir plusieurs colonnes avec le même générateur de séquence, créez la séquence indépendamment.