8.17. Types intervalle de valeurs

PostgreSQL fournit nativement les types intervalle de valeurs suivants :

Vous pouvez en plus définir vos propres types intervalle de valeurs ; voir CREATE TYPE(7) 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.43, « Opérateurs pour les types range » et Tableau 9.44, « Fonctions range » 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, « Saisie/affichage d'intervalle de valeurs » 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 borne inférieure d'un intervalle de valeurs peut être omise, signifiant que tous les points valant moins que la borne supérieure sont inclus dans l'intervalle. De la même façon, si la borne supérieure est omise, alors tous les points valant plus que la borne inférieure sont inclus dans l'intervalle. Si à la fois les bornes inférieures et supérieures sont omises, toutes les valeurs du type d'élément sont considérées comme étant dans l'intervalle.

Cela équivaut à considérer respectivement que la borne inférieure vaut « moins l'infini », ou que la borne supérieure vaut « plus l'infini ». Mais notez que ces valeurs infinies ne sont jamais des valeurs du type d'élément de l'intervalle, et ne peuvent jamais faire partie de l'intervalle (il n'y a donc pas de borne infinie inclusive -- si vous essayez d'en écrire une, elle sera automatiquement convertie en une borne exclusive).

Par ailleurs, des types d'élément ont une notion d'« infini », mais ce n'est qu'une valeur comme une autre, telle qu'elle est représentée dans le mécanisme de type intervalle de valeurs. Par exemple, dans des intervalles de timestamp, [today,] a la même signification que [today,). Mais [today,infinity] a une signification différente de [today,infinity) -- le second exclut la valeur spéciale de timestamp  infinity.

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]
vide

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 vide, ce qui représente un intervalle de valeurs 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, faîtes 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.)

Exemples :

-- inclue 3, n'inclue pas 7, et inclue tous les points entre
SELECT '[3,7)'::int4range;

-- n'inclue ni 3 ni 7, mais inclue tous les points entre
SELECT '(3,7)'::int4range;

-- n'inclue que l'unique point 4
SELECT '[4,4]'::int4range;

-- n'inclue aucun point (et sera normalisé à 'vide')
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 discretes 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 dit 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 notation 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 incluent les bornes inférieures et excluent 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.

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. La sortie canonique 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.

Définir votre propre type intervalle de valeurs vous permet également de spécifier une classe d'opérateur B-tree ou un collationnement différent à utiliser, pour pouvoir changer l'ordre de tri qui détermine les valeurs qui sont dans un intervalle donné.

De plus, chaque type intervalle de valeurs destiné à être utilisé avec des index GiST devrait définir une fonction de différence de sous-type, ou subtype_diff . (Un index GiST continuera à fonctionner sans subtype_diff, mais il est très probable qu'il sera vraiment moins efficace que si une fonction de différence est fournie.) La fonction de différence de sous-type prend en entrée deux valeurs du sous-type, et retourne leur différence (c'est-à-dire X moins Y) représenté comme une valeur float8. Dans notre exemple ci-dessus, la fonction sous-jacente à l'opérateur habituel de soustraction du type float8 peut être utilisée; mais pour n'importe quel autre sous-type, des conversions seraient nécessaires. Des idées créatives sur comment représenter des différences sous forme de nombres peuvent être également nécessaires. Pour être le plus complet, la fonction subtype_diff devrait être en accord avec l'ordre de tri impliqué par l'opérateur de classe et le collationnement sélectionnés ; pour le dire autrement, son résultat devrait être positif chaque fois que son premier argument est plus grand que son second d'après le sens du tri.

Voir CREATE TYPE(7) pour plus d'informations sur la façon de créer des type intervalles de valeurs.

Des index GiST peuvent être créés pour des colonnes de table de type intervalle de valeurs. Par exemple :

CREATE INDEX reservation_idx ON reservation USING gist (during);

Un index GiST peut accélérer les requêtes impliquant ces opérateurs d'intervalle de valeurs : =, &&, <@, @>, <<, >>, -|-, &< et &> (voir Tableau 9.43, « Opérateurs pour les types range » 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 :

ALTER TABLE reservation
  ADD 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
  ( 1108, '[2010-01-01 11:30, 2010-01-01 13:00)' );
INSERT 0 1

INSERT INTO reservation VALUES
  ( 1108, '[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 14:30:00, 2010-01-01 15:30: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 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