Les types intervalle de valeurs sont des types de données représentant un
intervalle de valeurs d'un certain type d'élément (appelé
sous-type de l'intervalle). Par exemple, des
intervalles de timestamp
pourraient être utilisés pour
représenter les intervalles de temps durant lesquels une salle de réunion
est réservée. Dans ce cas, le type de données est tsrange
(la version abrégée de « timestamp range »), et
timestamp
est le sous-type. Le sous-type doit avoir un tri
complet pour que les valeurs d'élément incluses soient bien définies, avant
ou après l'intervalle de valeurs.
Les types intervalle de valeurs sont utiles parce qu'ils représentent de nombreuses valeurs d'élément en une seule valeur d'intervalle, et que des concepts comme le chevauchement d'intervalles peuvent être exprimés clairement. L'utilisation d'intervalle de temps et de date pour des besoins de planification est l'exemple le plus parlant ; mais les intervalles de prix, intervalles de mesure pour un instrument et ainsi de suite peuvent également être utiles.
PostgreSQL fournit nativement les types intervalle de valeurs suivants :
INT4RANGE
-- Intervalle d'integer
INT8RANGE
-- Intervalle de bigint
NUMRANGE
-- Intervalle de numeric
TSRANGE
-- Intervalle de timestamp without time zone
TSTZRANGE
-- Intervalle de timestamp with time zone
DATERANGE
-- Intervalle de date
Vous pouvez en plus définir vos propres types intervalle de valeurs ; voir CREATE TYPE pour plus d'informations.
CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES ( 1108, '[2010-01-01 14:30, 2010-01-01 15:30)' ); -- Inclusion SELECT int4range(10, 20) @> 3; -- Chevauchement SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0); -- Extraire la borne inférieure SELECT upper(int8range(15, 25)); -- Calculer l'intersection SELECT int4range(10, 20) * int4range(15, 25); -- Est-ce que l'intervalle est vide ? SELECT isempty(numrange(1, 5));
Voir Tableau 9.50 et Tableau 9.51 pour la liste complète des opérateurs et fonctions sur les types intervalle de valeurs.
Chaque intervalle de valeurs non vide a deux bornes, la borne inférieure et la borne supérieure. Tous les points entre ces valeurs sont inclus dans l'intervalle. Une borne inclusive signifie que le point limite lui-même est également inclus dans l'intervalle, alors qu'une borne exclusive signifie que ce point limite n'est pas inclus dans l'intervalle.
Dans un intervalle affiché sous la forme de texte, une borne inclusive
inférieure est représentée par « [
» tandis
qu'une borne exclusive inférieure est représentée par
« (
». De la même façon, une borne inclusive
supérieure est représentée par « ]
» tandis
qu'une borne exclusive supérieure est représentée par
« )
».
(Voir Section 8.17.5 pour plus de détails.)
Les fonctions lower_inc
et
upper_inc
testent respectivement si les bornes
inférieures et supérieures d'une valeur d'intervalle sont inclusives.
La limite basse d'un intervalle peut être omise, signifiant que toutes les
valeurs inférieures à la limite haute sont inclues dans l'intervalle, par
exemple (,3]
. De la même façon, si la limite haute d'un
intervalle est omise, alors toutes les valeurs supérieures à la limite
basse sont inclues dans l'intervalle. Si les limites basse et haute sont
omises, toutes les valeurs du type de l'élément sont considérées faire
partie de l'intervalle. Indiquer une limite manquante comme inclus fait
qu'elle est automatique convertie en exclus, autrement dit
[,]
est converti en (,)
. Vous pouvez
penser à ces valeurs manquantes comme +/-infinity, mais ce sont des valeurs
spéciales du type intervalle et sont considérées au delà des valeurs
+/-infinity du type de l'élément.
Les types de l'élément qui ont une notion de « infinity »
peuvent les utiliser comme limites explicites. Par exemple, pour les
intervalles du type timestamp, [today,infinity)
exclut
la valeur infinity
du type timestamp
, alors
que [today,infinity]
l'inclut, comme le font
[today,)
et [today,]
.
Les fonctions lower_inf
et upper_inf
testent respectivement si
les bornes inférieure et supérieure sont infinies.
La saisie d'un intervalle de valeurs doit suivre un des modèles suivants:
(borne-inférieure
,borne-supérieure
) (borne-inférieure
,borne-supérieure
] [borne-inférieure
,borne-supérieure
) [borne-inférieure
,borne-supérieure
] empty
Les parenthèses ou crochets indiquent si les bornes inférieure et supérieure
sont exclusives ou inclusives, comme décrit précédemment.
Notez que le modèle final est empty
, ce qui représente un
intervalle vide (un intervalle qui ne contient aucun point).
La borne-inférieure
peut être une chaîne
de caractères valide pour la saisie du sous-type, ou vide pour indiquer
qu'il n'y a pas de borne inférieure. De la même façon, la
borne-supérieure
peut être une chaîne de caractères
valide pour la saisie du sous-type, ou vide pour indiquer qu'il n'y a pas de
borne supérieure.
Chaque borne peut être protégée en entourant la valeur de guillemet double
("
). C'est nécessaire si la valeur de borne contient des
parenthèses, crochets, virgules, guillemets doubles, antislash, puisque, sans
cela, ces caractères seraient considérés comme faisant partie de la
syntaxe de l'intervalle de valeurs. Pour mettre un guillemet double ou un antislash
dans une valeur de borne protégée, faites-le précéder d'un antislash.
(Une paire de guillemets doubles dans une borne protégée est également
valable pour représenter un caractère guillemet double, de la même manière
que la règle pour les guillemets simples dans les chaînes SQL littérales.)
Vous pouvez éviter l'emploi des guillemets doubles en échappant avec un
antislash tous les caractères qui, sans cela, seraient pris comme une syntaxe
d'intervalle de valeurs. De plus, pour écrire une valeur de borne qui est une chaîne
vide, écrivez ""
, puisque ne rien écrire signifie
une borne infinie.
Des espaces sont autorisés avant et après la valeur de borne, mais chaque espace entre les parenthèses ou les crochets fera partie de la valeur de limite inférieure ou supérieure. (Selon le type d'élément, cela peut être ou ne pas être significatif.)
Ces règles sont très proches de celles de l'écriture de valeurs de champs pour les types composites. Voir Section 8.16.6 pour des commentaires supplémentaires.
Exemples :
-- inclut 3, n'inclut pas 7, et inclut tous les points entre SELECT '[3,7)'::int4range; -- n'inclut ni 3 ni 7, mais inclut tous les points entre SELECT '(3,7)'::int4range; -- n'inclut que l'unique point 4 SELECT '[4,4]'::int4range; -- n'inclut aucun point (et sera normalisé à 'empty') SELECT '[4,4)'::int4range;
Chaque type intervalle de valeurs a une fonction constructeur du même nom que
le type intervalle. Utiliser le constructeur est souvent plus pratique
que d'écrire une constante d'intervalle littérale puisque cela évite
d'avoir à ajouter des guillemets doubles sur les valeurs de borne. Le
constructeur accepte deux ou trois arguments. La forme à deux arguments
construit un intervalle dans sa forme standard (borne inférieure inclusive,
borne supérieure exclusive), alors que la version à trois arguments construit
un intervalle avec des bornes de la forme spécifiée par le troisième argument.
Le troisième argument doit être la chaîne
« ()
»,
« (]
»,
« [)
» ou
« []
».
Par exemple :
-- La forme complète est : borne inférieure, borne supérieure et argument texte indiquant -- inclusivité/exclusivité des bornes. SELECT numrange(1.0, 14.0, '(]'); -- Si le troisième argument est omis, '[)' est supposé. SELECT numrange(1.0, 14.0); -- Bien que '(]' soit ici spécifié, à l'affichage la valeur sera convertie en sa forme -- canonique puisque int8range est un type intervalle discret (voir ci-dessous). SELECT int8range(1, 14, '(]'); -- Utiliser NULL pour n'importe laquelle des bornes a pour effet de ne pas avoir de borne de ce côté. SELECT numrange(NULL, 2.2);
Un type d'intervalle de valeurs discrètes est un intervalle dont le type
d'élément a un « pas » bien défini, comme integer
ou
date
. Pour ces types, deux éléments peuvent être dits
comme étant adjacents, quand il n'y a pas de valeur valide entre eux.
Cela contraste avec des intervalles continus, où il y a toujours (ou
presque toujours) des valeurs d'autres éléments possibles à identifier
entre deux valeurs données. Par exemple, un intervalle de type
numeric
est continu, comme l'est un intervalle de type
timestamp
. (Même si timestamp
a une limite
de précision, et pourrait théoriquement être traité comme discret, il
est préférable de le considérer comme continu puisque la taille du pas
n'a normalement pas d'intérêt.)
Une autre façon d'imaginer un type d'intervalle de valeurs discrètes est qu'il
est possible de déterminer clairement une valeur « suivante » ou
« précédente » pour chaque valeur d'élément. En sachant cela,
il est possible de convertir des représentations inclusives et exclusives
d'une borne d'intervalle, en choisissant la valeur d'élément suivante
ou précédente à la place de celle d'origine.
Par exemple, dans un type d'intervalle entier, [4,8]
et (3,9)
représentent le même ensemble de valeurs,
mais cela ne serait pas le cas pour un intervalle de numeric.
Un type d'intervalle discret devrait avoir une fonction de mise en forme canonique consciente de la taille du pas désiré pour le type d'élément. La fonction de mise en forme canonique est chargée de convertir des valeurs équivalentes du type d'intervalle pour avoir des représentations identiques, surtout aux voisinages de bornes inclusives ou exclusives. Si une fonction de mise en forme canonique n'est pas spécifiée, alors les intervalles de notations différentes seront toujours traités comme étant différents, même s'ils peuvent en réalité représenter le même ensemble de valeurs.
Les types d'intervalle prédéfinis int4range
, int8range
,
et daterange
utilisent tous une forme canonique qui inclut
les bornes inférieures et exclut les bornes supérieures ; c'est-à-dire
[)
. Les types intervalles définis par l'utilisateur
peuvent cependant utiliser d'autres conventions.
Les utilisateurs peuvent définir leurs propres types intervalle de valeurs.
La raison la plus commune de le faire est d'utiliser des intervalles de
sous-types non prédéfinis.
Par exemple, pour définir un nouveau type d'intervalle de valeurs du
sous-type float8
:
CREATE TYPE floatrange AS RANGE ( subtype = float8, subtype_diff = float8mi ); SELECT '[1.234, 5.678]'::floatrange;
Puisque float8
n'a pas de « pas » significatif,
nous ne définissons pas de fonction de mise en forme canonique dans
cet exemple.
Définir votre propre type intervalle vous permet aussi de spécifier une classe différente d'opérateur ou un collationnement différent, à utiliser, pour modifier l'ordre de tri qui détermine les valeurs tombant dans un intervalle donné.
Si l'on considère que le sous-type est discret plutôt que continu, la
commande CREATE TYPE
devrait spécifier une fonction
canonique
.
La fonction de mise en forme canonique prend une valeur d'intervalle en
entrée, et doit retourner une valeur d'intervalle équivalente qui peut avoir
des bornes et une représentation différente.
Les sorties canoniques de deux intervalles qui représentent le même ensemble
de valeurs, par exemple les intervalles d'entier [1, 7]
et [1,8)
doivent être identiques. La représentation choisie
n'a pas d'importance, du moment que deux valeurs équivalentes avec des
représentations différentes sont toujours liées à la même valeur avec la
même représentation. En plus d'ajuster le format des bornes inclusives
et exclusives, une fonction de mise en forme canonique peut arrondir une
valeur de borne, dans le cas où la taille de pas désirée est plus grande
que ce que le sous-type est capable de stocker. Par exemple, un intervalle
de timestamp
pourrait être défini pour avoir une taille de pas
d'une heure, et dans ce cas la fonction de mise en forme canonique nécessiterait
d'arrondir les bornes qui ne sont pas multiples d'une heure, ou peut-être
déclencher une erreur à la place.
De plus, tout type intervalle devant être utilisé avec des index GiST ou
SP-GiST doit définir une différence de sous-type ou une fonction
subtype_diff
. (L'index fonctionnera toujours sans
fonction subtype_diff
, mais il y a de fortes chances
qu'il soit considérablement moins efficace qu'avec une fonction de
différence.) La fonction de différence du sous-type prend deux valeurs en
entrée et renvoie leur différence (par exemple,
X
moins Y
)
représentée sous la forme d'une valeur de type float8
. Dans
notre exemple ci-dessus, la fonction float8mi
qui
soutient l'opérateur moins du type float8
peut être
utilisée ; mais pour tout autre sous-type, une conversion de type
serait nécessaire. Un peu de créativité peut se révéler nécessaire pour
représenter la différence sous une forme numérique. Dans la mesure du
possible, la fonction subtype_diff
devrait être en
accord avec l'ordre de tri impliqué par la classe d'opérateur et le
collationnement sélectionnés ; autrement dit, son résultat doit être
positif quand le premier argument est supérieur au second d'après l'ordre
de tri.
Voici un exemple moins simplifié d'une fonction
subtype_diff
:
CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS 'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE; CREATE TYPE timerange AS RANGE ( subtype = time, subtype_diff = time_subtype_diff ); SELECT '[11:10, 23:00]'::timerange;
Voir CREATE TYPE pour plus d'informations sur la façon de créer des types intervalle de valeurs.
Des index GiST et SP-GiST peuvent être créés pour des colonnes de table de type intervalle de valeurs. Par exemple, pour créer un index GiST :
CREATE INDEX reservation_idx ON reservation USING GIST (during);
Un index GiST ou SP-GiST peut accélérer les requêtes impliquant ces opérateurs
d'intervalle de valeurs :
=
,
&&
,
<@
,
@>
,
<<
,
>>
,
-|-
,
&<
et
&>
(voir Tableau 9.50 pour plus d'informations).
De plus, les index B-tree et hash peuvent être créés pour des colonnes d'une table
de type intervalle de valeurs. Pour ces types d'index, la seule opération d'intervalle
véritablement utile est l'égalité. Il y a un ordre de tri pour les index B-tree définis
pour les valeurs d'intervalle, correspondant aux opérateurs <
et >
, mais le tri est plutôt arbitraire et généralement inutile
dans la réalité. Le support de B-tree et hash pour les types intervalle de valeurs
est à la base destiné à permettre le tri et le hachage de façon interne dans les
requêtes, plutôt que pour la création d'un vrai index.
Bien que UNIQUE
soit une contrainte naturelle pour
des valeurs scalaires, c'est en générale inutilisable pour des types
intervalle de valeurs. À la place, une contrainte d'exclusion est
souvent plus appropriée
(voir CREATE TABLE
... CONSTRAINT ... EXCLUDE). Les contraintes d'exclusion
permettent la spécification de contraintes telles que le
« non chevauchement » sur un type intervalle de valeurs.
Par exemple :
CREATE TABLE reservation ( during tsrange, EXCLUDE USING GIST (during WITH &&) );
Cette contrainte empêchera toute valeur chevauchant une autre présente dans la table à la même heure :
INSERT INTO reservation VALUES ('[2010-01-01 11:30, 2010-01-01 15:00)'); INSERT 0 1 INSERT INTO reservation VALUES ('[2010-01-01 14:45, 2010-01-01 15:45)'); ERROR: conflicting key value violates exclusion constraint "reservation_during_excl" DETAIL: Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).
Vous pouvez utiliser l'extension btree_gist
pour définir une
contrainte d'exclusion sur des types de données scalaires, qui peuvent alors
être combinés avec des exclusions d'intervalle de valeurs pour un maximum de
flexibilité. Par exemple, une fois que btree_gist
est
installé, la contrainte suivante ne rejettera les intervalles de valeurs se
chevauchant que si le numéro de la salle de conférence est identique :
CREATE EXTENSION btree_gist; CREATE TABLE room_reservation ( room TEXT, during TSRANGE, EXCLUDE USING GIST (room WITH =, during WITH &&) ); INSERT INTO room_reservation VALUES ( '123A', '[2010-01-01 14:00, 2010-01-01 15:00)' ); INSERT 0 1 INSERT INTO room_reservation VALUES ( '123A', '[2010-01-01 14:30, 2010-01-01 15:30)' ); ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" DETAIL: Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")). INSERT INTO room_reservation VALUES ( '123B', '[2010-01-01 14:30, 2010-01-01 15:30)' ); INSERT 0 1