Menu

Clé primaire PostgreSQL : IDENTITY, SERIAL et UUID

Comment définir une clé primaire dans PostgreSQL et comment la générer : colonnes identity, l'ancien SERIAL et les UUID avec gen_random_uuid(). Aussi les clés composites et la remise à niveau d'une séquence.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

Une clé primaire est la colonne, ou l'ensemble de colonnes, qui identifie chaque ligne. Ses valeurs doivent être uniques et non NULL, et une table ne peut en avoir qu'une. Dans PostgreSQL, on laisse en général la base la générer avec une colonne identity : id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY.

Les id valent 1 et 2. Définir la clé crée aussi un index unique dessus, nommé users_pkey.

Colonnes identity : ALWAYS ou BY DEFAULT

Les colonnes identity sont la façon standard en SQL de numéroter les lignes, disponible depuis PostgreSQL 10. Il en existe deux formes :

  • GENERATED ALWAYS AS IDENTITY refuse une valeur explicite dans un INSERT, sauf si l'insertion précise OVERRIDING SYSTEM VALUE. C'est le choix par défaut le plus sûr.
  • GENERATED BY DEFAULT AS IDENTITY n'utilise la valeur générée que si vous n'en fournissez pas.

Avec BY DEFAULT, insérer vos propres id laisse la séquence en retard, et le prochain id généré entre en collision. Après avoir importé des lignes avec des id explicites, faites avancer la séquence au delà du plus grand avec setval :

Sans la ligne setval, la dernière insertion recevrait l'id 1 et échouerait sur une erreur de clé en double. Avec elle, la nouvelle ligne reçoit 4.

SERIAL ou identity

serial (4 octets) et bigserial (8 octets) ne sont pas de vrais types. Ce sont des raccourcis qui créent une séquence et donnent à la colonne la valeur par défaut nextval() de cette séquence :

La colonne est un simple integer avec la valeur par défaut nextval('legacy_id_seq'::regclass). Rien n'empêche une insertion d'écrire son propre id, et la séquence a besoin de ses propres droits. Les colonnes identity règlent ces deux problèmes, donc préférez-les dans les nouvelles tables. serial fonctionne toujours, et vous le croiserez dans des schémas et des tutoriels plus anciens.

Utilisez bigint (ou bigserial) pour les tables qui peuvent grossir. Une clé integer s'épuise après environ 2,1 milliards de valeurs.

Clés primaires UUID

gen_random_uuid() renvoie un UUID aléatoire de version 4. La fonction est intégrée depuis PostgreSQL 13, donc aucune extension n'est nécessaire :

Chaque exécution affiche des id différents, de 36 caractères chacun sous forme de texte. Le type uuid les stocke sur 16 octets, donc utilisez-le plutôt que text. PostgreSQL 18 ajoute uuidv7(), qui génère des UUID ordonnés dans le temps, insérés dans l'ordre de l'index. Sur PostgreSQL 17, générez-les dans votre application.

Clés primaires composites

Une clé peut couvrir plusieurs colonnes ; on l'écrit alors sur sa propre ligne dans CREATE TABLE. C'est la clé naturelle d'une table de liaison, où chaque paire ne peut apparaître qu'une fois :

La paire répétée (1, 10) est ignorée, donc le total vaut 3. Pour ajouter une clé à une table existante, utilisez ALTER TABLE t ADD PRIMARY KEY (col). La page ALTER TABLE couvre les autres modifications. Les autres tables pointent vers la clé avec des clés étrangères.

Questions fréquentes

PostgreSQL a-t-il un AUTO_INCREMENT ?

Pas sous ce nom. La méthode standard est une colonne identity, id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, disponible depuis PostgreSQL 10. Les anciens types serial et bigserial font la même chose avec une séquence et une valeur par défaut.

Pourquoi ma séquence d'id PostgreSQL a-t-elle des trous ?

Une séquence distribue une valeur même quand l'insertion échoue ensuite ou que sa transaction est annulée, et cette valeur n'est jamais réutilisée. Les trous sont normaux et sans danger, donc ne comptez pas sur des id consécutifs.

Une clé primaire crée-t-elle un index dans PostgreSQL ?

Oui. Une clé primaire (comme toute contrainte UNIQUE) crée automatiquement un index B-tree unique, nommé tablename_pkey pour la clé primaire.

Faut-il choisir UUID ou bigint pour une clé primaire ?

Les clés identity en bigint sont plus petites (8 octets contre 16) et s'insèrent dans l'ordre de l'index. Les UUID se génèrent n'importe où sans aller-retour vers la base et ne révèlent pas le nombre de lignes. Les UUID aléatoires dispersent les insertions dans tout l'index, ce qui coûte plus cher sur les grandes tables.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer