Lors de la modélisation des données, il est fréquent d’utiliser des clés primaires avec un auto-incrément, ce qui permet d’obtenir facilement un identifiant unique pour chaque enregistrement.

Avec PostgreSQL, la méthode habituelle est d’utiliser la séquence et les fonctions associées, souvent masquées par l’utilisation des pseudo-types serial et bigserial.

La version 10 de PostgreSQL conserve le mécanisme des séquences et ajoute la notion de colonne d’identité, qui correspond à ce qui est décrit dans la norme ISO SQL:2003.

Dans l’ensemble, le fonctionnement est le même, car la colonne d’identité utilise effectivement une séquence, et il est possible d’utiliser une séquence existante.

Mais la colonne d’identité apporte la possibilité d'être plus strict avec les données insérées qu’avec la méthode précédente.

Le type serial et les séquences

Tout d’abord, revenons sur la méthode existant avant la version 10 : les pseudo-types smallserial, serial et bigserial.

L’exemple suivant crée une table servant à stocker la valeur numérique d’un événement :

CREATE TABLE event_store
(
    id   SERIAL
    , ev TIMESTAMPTZ
    , v  NUMERIC
);

Ensuite, l’affichage du modèle de la table, avec la commande \d de l’outil psql, montre ce qui est effectivement fait :

\d event_store
                 Table "public.event_store"
 Column |           Type           |                 Default
--------+--------------------------+-----------------------------------------
 id     | integer                  | nextval('event_store_id_seq'::regclass)
 ev     | timestamp with time zone |
 v      | numeric                  |

On voit bien que la colonne id est en fait de type integer, et que la valeur par défaut est obtenue par l’appel de la fonction nextval() avec en argument le nom d’une séquence : event_store_id_seq. Cette séquence est automatiquement créée en même temps que la table :

\d event_store_id_seq
                 Sequence "public.event_store_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Owned by: public.event_store.id

À partir de ce moment, les insertions de données peuvent utiliser la valeur par défaut de la colonne, et donc incrémenter la séquence.

Colonne d’identité

Avec la colonne d’identité, la syntaxe change, mais le résultat est très similaire.

Le type de données INTEGER est explicitement mentionné, et ce sont les mots clés GENERATED AS IDENTITY qui font qu’une séquence est mise en place :

CREATE TABLE event_store
(
    id   INTEGER GENERATED BY DEFAULT AS IDENTITY
    , ev TIMESTAMPTZ
    , v  NUMERIC
);

Le comportement est le même qu’avec une séquence, de par l’utilisation du mot-clé BY DEFAULT. Nous verrons ensuite ce qui peut changer à ce sujet.

La table a alors la description suivante dans psql :

\d event_store
                                 Table "public.event_store"
 Column |           Type           |             Default
--------+--------------------------+----------------------------------
 id     | integer                  | generated by default as identity
 ev     | timestamp with time zone |
 v      | numeric                  |

La séquence est créée, et est liée à la colonne d’identité :

\d event_store_id_seq
                 Sequence "public.event_store_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1
Sequence for identity column: public.event_store.id

Le nom de la séquence n’apparait plus dans la description de la table, car il n’est pas fait mention de la méthode nextval() utilisée. Pour trouver la séquence lié a la table, il est possible d’utiliser la table pg_depend du catalogue, comme dans la requête suivante :

SELECT nspname ||'.'|| relname as seq_name
    FROM pg_class c
      JOIN pg_namespace n
        ON c.relnamespace=n.oid
    WHERE relkind='S'
      AND c.oid IN
      (
        SELECT d.objid
            FROM pg_class c
              JOIN pg_namespace n
                ON c.relnamespace=n.oid
              JOIN pg_depend d
                ON c.oid=d.refobjid
              WHERE c.relname='event_store'
                AND n.nspname='public'
      )
;
         seq_name
---------------------------
 public.event_store_id_seq
(1 row)

Utilisation

Le but est de produire une valeur pour chaque nouvel enregistrement créé dans la table. L’appel de l’ordre INSERT permet alors de récupérer une nouvelle valeur :

=> BEGIN ;
BEGIN

=>* INSERT INTO event_store ( id, ev, v ) values ( DEFAULT, now(), pi() ) ;
INSERT 0 1

=>* INSERT INTO event_store ( id, ev, v ) values ( DEFAULT, now(), pi() ) ;
INSERT 0 1

=>* select * from event_store ;
 id |              ev               |        v
----+-------------------------------+------------------
  1 | 2019-05-16 17:32:56.290646+02 | 3.14159265358979
  2 | 2019-05-16 17:32:56.290646+02 | 3.14159265358979
(2 rows)

=>* select * from event_store_id_seq ;
 last_value | log_cnt | is_called
------------+---------+-----------
          2 |      31 | t
(1 row)

=>* COMMIT ;
COMMIT

Le mot clé DEFAULT déclenche la récupération de la valeur : on constate que la séquence est bien incrémentée.

Il est aussi possible de ne pas spécifier la colonne id dans l’ordre INSERT :

INSERT INTO event_store ( ev, v ) values ( now(), pi() ) ;

Durcissement du contrôle

Sans contrainte de clé primaire ou unique, rien ne protège la colonne d’identité contre l’utilisation d’une valeur qui ne serait pas issue de la séquence, comme le montre l’exemple suivant :

=> INSERT INTO event_store ( id, ev, v ) values ( 2, now(), pi() ) ;
INSERT 0 1

=> select * from event_store ;
 id |              ev               |        v
----+-------------------------------+------------------
  1 | 2019-05-16 17:32:56.290646+02 | 3.14159265358979
  2 | 2019-05-16 17:32:56.290646+02 | 3.14159265358979
  2 | 2019-05-16 17:41:08.625941+02 | 3.14159265358979
(3 rows)

Pour éviter cela, il est possible de forcer l’utilisation de la séquence, avec le mot clé ALWAYS :

CREATE TABLE event_store
(
    id   INTEGER GENERATED ALWAYS AS IDENTITY
    , ev TIMESTAMPTZ
    , v  NUMERIC
);

Ou la modification suivante :

ALTER TABLE event_store
    ALTER COLUMN id SET GENERATED ALWAYS ;

L’ordre INSERT précédent échoue alors :

=> INSERT INTO event_store ( id, ev, v ) values ( 2, now(), pi() ) ;
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Ceci protège donc l’utilisation de la séquence.

Comme indiqué dans le message d’erreur, il est possible d’outrepasser cette limitation en réécrivant l’ordre INSERT :

INSERT INTO event_store(id, ev, v)
    OVERRIDING SYSTEM VALUE
    values( 2, NOW(), 1 ) ;

Conclusion

Si la colonne d’identité ne change pas fondamentalement, comparée à l’usage du pseudo-type SERIAL, il permet de se rapprocher du standard SQL, tout en renforçant l’intégrité des données.

Par ailleurs, dans la version 12 de PostgreSQL, il sera possible de définir une expression pour alimenter une colonne avec une valeur générée.