Chaque colonne PostgreSQL a un type fixe, défini dans CREATE TABLE. La plupart des tables n'en demandent qu'une poignée : bigint ou integer pour les nombres entiers, numeric pour l'argent, text pour les chaînes, boolean, timestamptz pour les instants, et jsonb pour les documents. pg_typeof() vous donne le type de n'importe quelle valeur.
Les principaux types et quand les utiliser
| Type | Contient | À utiliser pour |
|---|---|---|
integer (int) | Entiers jusqu'à environ 2,1 milliards, 4 octets | Comptes, quantités, petits identifiants |
bigint | Entiers jusqu'à environ 9,2 trillions, 8 octets | Clés primaires, tout ce qui peut grandir |
smallint | Jusqu'à 32767, 2 octets | Rarement utile |
numeric(p, s) | Décimaux exacts | Argent, taux, tout ce qu'on additionne |
real, double precision | Virgule flottante binaire | Mesures, valeurs scientifiques |
text | Chaînes de n'importe quelle longueur | Noms, e-mails, descriptions |
varchar(n) | Chaînes avec une longueur maximale | Quand la limite est une règle |
boolean | true, false ou NULL | Indicateurs |
date | Un jour du calendrier | Anniversaires, échéances |
timestamptz | Un instant précis | Dates de création et de mise à jour |
interval | Une durée | '2 hours', '30 days' |
uuid | Identifiant de 128 bits | Identifiants publics ou distribués |
jsonb | JSON analysé | Données sans forme fixe |
text[], int[] | Tableaux de n'importe quel type | Listes courtes, tags |
bytea | Octets bruts | Petites valeurs binaires |
Les choix les plus importants ont leur propre page : timestamp ou timestamptz, types enum, jsonb et les tableaux. Les identifiants (serial, colonnes identity, uuid) sont traités avec les clés primaires.
numeric ou double precision
numeric stocke les décimaux de façon exacte. real et double precision stockent des fractions binaires, donc des valeurs comme 0.1 sont des approximations et les sommes peuvent dériver :
La somme en numeric vaut 0.3. La somme en virgule flottante vaut 0.30000000000000004, et le test d'égalité donne false. Utilisez numeric pour l'argent : numeric(10, 2) autorise 10 chiffres au total, dont 2 après la virgule, et arrondit les décimales en trop à l'insertion.
Diviser un entier par un entier supprime la partie décimale :
7 / 2 renvoie 3. Rendez l'un des deux côtés numeric (un littéral avec un point décimal, ou une conversion) pour obtenir 3.5.
text, varchar(n) ou char(n)
Les trois utilisent le même stockage, et aucun n'est plus rapide. varchar(n) rejette les valeurs plus longues à l'insertion, ce qui n'aide que si la limite est une règle de vos données. char(n) complète avec des espaces et n'a aucune utilité dans une nouvelle table. Une conversion explicite en varchar(n) tronque au lieu d'échouer.
La conversion renvoie Post. La valeur char(5) occupe 5 octets, mais length ignore le remplissage et renvoie 2.
Valeurs booléennes et NULL
Une colonne boolean accepte true, false, 't', 'yes', 'on', '1' et leurs contraires, et renvoie toujours true ou false. Une valeur manquante est NULL, qui n'est ni l'un ni l'autre, donc WHERE NOT active ignore les lignes où active vaut NULL.
Utilisez IS TRUE, IS NOT TRUE ou COALESCE(active, false) quand NULL doit compter comme faux.
Questions fréquentes
Y a-t-il une différence de performance entre text et varchar dans PostgreSQL ?
Non. text, varchar et varchar(n) sont stockés de la même façon. varchar(n) ajoute seulement une vérification de longueur, donc utilisez text sauf si la limite est une vraie règle de vos données.
Faut-il utiliser le type money dans PostgreSQL ?
En général, non. Son entrée et sa sortie dépendent du paramètre lc_monetary, et une valeur n'enregistre pas sa devise. Stockez les montants en numeric(12,2) et gardez la devise dans sa propre colonne.
Quel est l'équivalent PostgreSQL de DATETIME ?
timestamptz (timestamp with time zone) pour un instant précis, ou timestamp pour une heure d'horloge sans fuseau. PostgreSQL n'a pas de type DATETIME.
Comment voir le type d'une valeur ou d'une colonne dans PostgreSQL ?
SELECT pg_typeof(expression) renvoie le type de n'importe quelle expression, y compris une colonne. Dans psql, \d table_name liste chaque colonne avec son type.