CREATE SEQUENCE — Définir un nouveau générateur de séquence
CREATE [ { TEMPORARY | TEMP } | UNLOGGED ] SEQUENCE [ IF NOT EXISTS ] name
[ AS type_donnee ]
[ INCREMENT [ BY ] increment ]
[ MINVALUE valeurmin | NO MINVALUE ]
[ MAXVALUE valeurmax | NO MAXVALUE ]
[ START [ WITH ] début ]
[ CACHE cache ]
[ [ NO ] CYCLE ]
[ OWNED BY { nom_table.nom_colonne | NONE } ]
CREATE SEQUENCE crée un nouveau générateur de
séquence de nombres. Cela implique la création et l'initialisation d'une nouvelle table
à une seule ligne nommée nom. Le générateur appartient à
l'utilisateur qui exécute la commande.
Si un nom de schéma est donné, la séquence est créée dans le schéma spécifié. Sinon, elle est créée dans le schéma courant. Les séquences temporaires existent dans un schéma spécial, il n'est donc pas utile de préciser un nom de schéma lors de la création d'une séquence temporaire. Le nom de la séquence doit être distinct du nom de toute autre relation (table, séquence, index, vue, vue matérialisée ou table distante) du même schéma.
Après la création d'une séquence, les fonctions
nextval, currval et
setval sont utilisées pour agir sur la séquence. Ces fonctions sont
documentées dans Section 9.17.
Bien qu'il ne soit pas possible de mettre à jour une séquence en accédant directement à la table, une requête telle que :
SELECT * FROM nom;
peut être utilisée pour examiner les paramètres et l'état courant d'une séquence. En
particulier, le champ last_value affiche la
dernière valeur allouée par une session. (Cette valeur peut être rendue obsolète à
l'affichage par des appels effectifs de nextval dans des sessions
concurrentes.)
TEMPORARY ou TEMPSi ce paramètre est spécifié, l'objet séquence n'est créé que pour la session en cours et est automatiquement supprimé lors de la sortie de session. Les séquences permanentes portant le même nom ne sont pas visibles (dans cette session) tant que la séquence temporaire existe, sauf à être référencées par les noms qualifiés du schéma.
UNLOGGEDSi indiqué, la séquence est créée comme une séquence non journalisée. Les modifications d'une séquence non journalisée ne sont pas écrites dans les journaux de transactions. Leur état n'est donc pas garanti en cas de crash : une séquence non journalisée est automatiquement ré-initialisée à son état initial après un crash ou un arrêt non propre. Les séquences non journalisées ne sont pas répliquées vers des serveurs secondaires.
Contrairement aux tables non journalisées, les séquences non journalisées
n'offrent pas d'avantages significatifs pour les performances. Cette
option a principalement pour objectif d'être associée aux tables non
journalisées via des colonnes d'identité ou des colonnes de type
serial Dans ces cas, cela n'aurait pas de sens d'avoir
la séquence journalisée et répliquée mais pas sa table associée.
IF NOT EXISTSNe renvoie pas une erreur si une relation de même nom existe déjà. Un message d'avertissement est renvoyé dans ce cas. Notez qu'il n'y a aucune garantie que la relation existante ressemble à la séquence qui aurait été créée. Il est même possible que cela ne soit pas une séquence.
nomLe nom (éventuellement qualifié du nom du schéma) de la séquence à créer.
type_donnee
La clause facultative AS
spécifie le type de donnée de la séquence. Les types valides sont
type_donneesmallint, integer,
et bigint. bigint est le type par
défault. Le type de donnée détermine les valeurs minimales et maximales
par défaut pour la séquence.
incrément
La clause optionnelle
INCREMENT BY
précise la
valeur à ajouter à la valeur courante de la séquence pour créer une
nouvelle valeur. Une valeur positive crée une séquence ascendante, une
valeur négative une séquence descendante. 1 est la valeur par défaut.
incrément
valeurminNO MINVALUE
La clause optionnelle
MINVALUE
détermine la valeur
minimale de la séquence. Si cette clause n'est pas fournie
ou si valeurminNO MINVALUE est spécifié, alors les valeurs par
défaut sont utilisées. La valeur par défaut pour une séquence
ascendante est 1. La valeur par défaut pour une séquence descendante est
la valeur minimale du type de donnée.
valeurmaxNO MAXVALUE
La clause optionnelle MAXVALUE détermine la valeur
maximale de la séquence. Si cette clause n'est pas fournie ou si
valeurmaxNO MAXVALUE est spécifié, alors les valeurs par défaut
sont utilisées. La valeur par défaut pour une séquence ascendante est la
valeur maximale pour le type de données. La valeur par défaut pour une
séquence descendante est -1.
début
La clause optionnelle
START WITH
permet à la séquence de démarrer n'importe où.
La valeur de début par défaut est
début valeurmin pour les séquences ascendantes
et valeurmax pour les
séquences descendantes.
cache
La clause optionnelle
CACHE
spécifie le nombre de numéros de séquence à préallouer et stocker en mémoire pour un
accès plus rapide. 1 est la valeur minimale (une seule valeur est
engendrée à la fois, soit pas de cache) et la valeur
par défaut.
cache
CYCLENO CYCLE
L'option CYCLE autorise la séquence à recommencer au
début lorsque valeurmax ou
valeurmin sont atteintes, respectivement, par
une séquence ascendante ou descendante. Si la limite est
atteinte, le prochain nombre engendré est respectivement
valeurmin ou
valeurmax.
Si NO CYCLE est spécifié, tout appel à
nextval alors que la séquence a atteint la valeur
maximale (dans le cas d'une séquence ascendante) ou la valeur minimale (dans l'autre cas)
retourne une erreur. En l'absence de précision, NO CYCLE
est la valeur par défaut.
OWNED BY nom_table.nom_colonneOWNED BY NONE
L'option OWNED BY permet d'associer la séquence à une
colonne de table spécifique. De cette façon, la séquence sera
automatiquement supprimée si la colonne (ou la table entière) est supprimée.
La table indiquée doit avoir le même propriétaire et être dans le même
schéma que la séquence.
OWNED BY NONE, valeur par défaut, indique qu'il n'y a
pas d'association.
DROP SEQUENCE est utilisé pour supprimer une séquence.
Les séquences sont fondées sur l'arithmétique bigint, leur échelle
ne peut donc pas excéder l'échelle d'un entier sur huit octets
(-9223372036854775808 à 9223372036854775807).
Comme les appels à nextval et setval
ne sont jamais annulés, les objets séquences ne peuvent pas être utilisés
si des affectations « sans trous » sont nécessaires. Il est
possible de construire une affectation sans trou en utilisant des verrous
exclusifs sur une table contenant un compteur. Cependant, cette solution
est bien plus coûteuse que les objets séquences, tout spécialement si un
grand nombre de transactions ont besoin de numéro de séquence en parallèle.
Des résultats inattendus peuvent être obtenus dans le cas d'un
paramétrage de cache supérieur
à un pour une séquence utilisée concurrentiellement par
plusieurs sessions. Chaque session alloue et cache des valeurs de
séquences successives lors d'un accès à la séquence et augmente en conséquence la
valeur de last_value. Les cache-1
appels suivants de nextval au cours de la session
session retourne simplement les valeurs préallouées sans toucher à la
séquence. De ce fait, tout nombre alloué mais non utilisé au cours d'une session est
perdu à la fin de la session, créant ainsi des « trous » dans
la séquence.
De plus, bien qu'il soit garanti que des sessions différentes engendrent
des valeurs de séquence distinctes, si l'on considère toutes les sessions,
les valeurs peuvent ne pas être engendrées séquentiellement.
Par exemple, avec un paramétrage du
cache à 10, la session A peut réserver
les valeurs 1..10 et récupérer nextval=1 ; la
session B peut alors réserver les valeurs 11..20 et récupérer
nextval=11 avant que la session A n'ait engendré
nextval=2.
De ce fait, un paramétrage de cache à un
permet d'assumer que les valeurs retournées par nextval sont
engendrées séquentiellement ; avec un
cache supérieur,
on ne peut qu'assumer que les valeurs retournées par nextval
sont tous distinctes, non qu'elles sont réellement engendrées
séquentiellement. De plus, last_value reflète la dernière
valeur réservée pour toutes les sessions, que nextval ait ou non
retourné cette valeur.
D'autre part, setval exécuté sur une telle
séquence n'est pas pris en compte par les autres sessions avant qu'elle n'aient
utilisé toutes les valeurs préallouées et cachées.
Créer une séquence ascendante appelée serie, démarrant à
101 :
CREATE SEQUENCE serie START 101;
Sélectionner le prochain numéro de cette séquence :
SELECT nextval('serie');
nextval
---------
101
Récupérer le prochain numéro d'une séquence :
SELECT nextval('serial');
nextval
---------
102
Utiliser cette séquence dans une commande INSERT :
INSERT INTO distributors VALUES (nextval('serie'), 'nothing');
Mettre à jour la valeur de la séquence après un COPY
FROM :
BEGIN;
COPY distributeurs FROM 'fichier_entrees';
SELECT setval('serie', max(id)) FROM distributeurs;
END;
CREATE SEQUENCE est conforme au standard
SQL, exception faites des remarques suivantes :
Obtenir la prochaine valeur se fait en utilisant la fonction
nextval() au lieu de l'expression standard
NEXT VALUE FOR.
La clause OWNED BY est une extension
PostgreSQL.