PostgreSQL fournit nativement les types intervalle de valeurs suivants :

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.53 et Tableau 9.55 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 incluses 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 incluses 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.)

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;
   

La saisie d'un multirange se fait avec des accolades ({ et }) contenant zéro ou plusieurs ranges valides, séparés par des virgules. Les espaces blancs sont autorisés autour des accolades et des virgules. Ceci a pour but de rappeler la syntaxe des tableaux, bien que les multiranges sont bien plus simples : ils ont juste une dimension et il n'est pas nécessaire de mettre entre guillemets leur contenu. (Les limites de leur ranges pourraient être mises entre guillemets néanmoins.)

Exemples :

SELECT '{}'::int4multirange;
SELECT '{[3,7)}'::int4multirange;
SELECT '{[3,7), [8,9)}'::int4multirange;

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);

Chaque type range a aussi un constructeur multirange du même nom que le type multirange. La fonction constructeur prend zéro ou plus d'argumentsqui sont tous des intervalles du type approprié. Par exemple :

SELECT nummultirange();
SELECT nummultirange(numrange(1.0, 14.0));
SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));
   

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.

Quand vous définissez votre propre intervalle, vous obtenez automatiquement un type multirange correspondant.

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érateurs 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 ranges. Des index GiST peuvent aussi être créés pour les colonnes de types multirange. Par exemple, pour créer un index GiST :

CREATE INDEX reservation_idx ON reservation USING GIST (during);
   

Un index GiST ou SP-GiST sur un type range peut accélérer les requêtes impliquant ces opérateurs d'intervalle de valeurs : =, &&, <@, @>, <<, >>, -|-, &< et &>. Un index GiST sur des multiranges peut accélérer les requêtes impliquant le même ensemble d'opérateurs multirange. Un index GiST sur des ranges et un index GiST sur des multiranges peut aussi accélérer les requêtes impliquant les opérateurs inter types range vers multirange et multirange vers range : &&, <@, @>, <<, >>, -|-, &< et &>. voir Tableau 9.53 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