Ejemplo de tipos de rango de Postgresql

8.17.1. Tipos de rango integrados

8.17.2. Ejemplos de

8.17.3. Límites inclusivos y exclusivos

8.17.4. Rangos infinitos (ilimitados)

8.17.5. Rango de entrada / salida

8.17.6. Construyendo rangos

8.17.7. Tipos de rango discreto

8.17.8. Definición de nuevos tipos de rango

8.17.9. Indexación

8.17.10. Restricciones en rangos

Los tipos de rango son tipos de datos que representan un rango de valores de algún tipo de elemento (llamado rango subtipo). Por ejemplo, rangos de timestamp se puede utilizar para representar los intervalos de tiempo que se reserva una sala de reuniones. En este caso, el tipo de datos es tsrange (corto para “rango de marca de tiempo“), y timestamp es el subtipo. El subtipo debe tener un orden total para que esté bien definido si los valores de los elementos están dentro, antes o después de un rango de valores.

Los tipos de rango son útiles porque representan muchos valores de elementos en un solo valor de rango y porque conceptos como rangos superpuestos se pueden expresar claramente. El uso de rangos de fecha y hora para fines de programación es el ejemplo más claro; pero los rangos de precios, los rangos de medición de un instrumento, etc. también pueden ser útiles.

8.17.1. Tipos de rango integrados

PostgreSQL viene con los siguientes tipos de rango integrados:

• int4range – Gama de integer

• int8range – Gama de bigint

• numrange – Gama de numeric

• tsrange – Gama de timestamp without time zone

• tstzrange – Gama de timestamp with time zone

• daterange – Gama de date

Además, puede definir sus propios tipos de rango; consulte CREAR TIPO para obtener más información.

8.17.2. Ejemplos de

CREARMESA reserva (habitación En t, durante tsrange);INSERTARDENTRO reserva VALORES(1108,'[2010-01-01 14:30, 2010-01-01 15:30)');-- ContainmentSELECT int4range(10,20) @>3;-- OverlapsSELECT numrange(11.1,22.2)&& numrange(20.0,30.0);-- Extract the upper boundSELECT upper(int8range(15,25));-- Compute the intersectionSELECT int4range(10,20)* int4range(15,25);-- Is the range empty?SELECT isempty(numrange(1,5));

See Table 9.53 and Table 9.54 for complete lists of operators and functions on range types.

8.17.3. Inclusive and Exclusive Bounds

Every non-empty range has two bounds, the lower bound and the upper bound. All points between these values are included in the range. An inclusive bound means that the boundary point itself is included in the range as well, while an exclusive bound means that the boundary point is not included in the range.

In the text form of a range, an inclusive lower bound is represented by “[” while an exclusive lower bound is represented by “(”. Likewise, an inclusive upper bound is represented by “]“, mientras que un límite superior exclusivo está representado por “)“. (Consulte la Sección 8.17.5 para obtener más detalles).

Las funciones lower_inc y upper_inc probar la inclusividad de los límites inferior y superior de un valor de rango, respectivamente.

8.17.4. Rangos infinitos (ilimitados)

El límite inferior de un rango se puede omitir, lo que significa que todos los valores menores que el límite superior se incluyen en el rango, por ejemplo, (,3]. Del mismo modo, si se omite el límite superior del rango, todos los valores mayores que el límite inferior se incluyen en el rango. Si se omiten los límites inferior y superior, se considera que todos los valores del tipo de elemento están en el rango. Especificar un límite faltante como inclusivo se convierte automáticamente en exclusivo, por ejemplo, [,] se convierte en (,). Puede pensar en estos valores perdidos como +/- infinito, pero son valores de tipo de rango especial y se considera que están más allá de los valores +/- infinito de cualquier tipo de elemento de rango.

Tipos de elementos que tienen la noción de “infinito“ puede usarlos como valores enlazados explícitos. Por ejemplo, con rangos de marcas de tiempo, [today,infinity) excludes the special timestamp value infinity, while [today,infinity] incluirlo, al igual que [today,) and [today,].

Las funciones lower_inf y upper_inf prueba para límites infinitos inferior y superior de un rango, respectivamente.

8.17.5. Rango de entrada / salida

La entrada para un valor de rango debe seguir uno de los siguientes patrones:

(lower-bound,upper-bound)(lower-bound,upper-bound][lower-bound,upper-bound)[lower-bound,upper-bound]
empty

Los paréntesis o corchetes indican si los límites superior e inferior son exclusivos o inclusivos, como se describió anteriormente. Observe que el patrón final es empty, que representa un rango vacío (un rango que no contiene puntos).

los lower-bound puede ser una cadena que sea una entrada válida para el subtipo o estar vacía para indicar que no hay límite inferior. Igualmente, upper-bound puede ser una cadena que sea una entrada válida para el subtipo o estar vacía para indicar que no hay límite superior.

Cada valor límite se puede cotizar usando " (comillas dobles) caracteres. Esto es necesario si el valor enlazado contiene paréntesis, corchetes, comas, comillas dobles o barras diagonales inversas, ya que estos caracteres se tomarían de otro modo como parte de la sintaxis del rango. Para poner una comilla doble o una barra invertida en un valor de límite entre comillas, preceda con una barra invertida. (Además, un par de comillas dobles dentro de un valor de límite entre comillas dobles se toma para representar un carácter de comillas dobles, de manera análoga a las reglas para comillas simples en cadenas literales de SQL). todos los caracteres de datos que de otro modo se tomarían como sintaxis de rango. Además, para escribir un valor enlazado que sea una cadena vacía, escriba "", ya que escribir nada significa un límite infinito.

Se permiten espacios en blanco antes y después del valor del rango, pero cualquier espacio en blanco entre paréntesis o corchetes se toma como parte del valor del límite superior o inferior. (Dependiendo del tipo de elemento, puede ser significativo o no).

Nota

Estas reglas son muy similares a las que se utilizan para escribir valores de campo en literales de tipo compuesto. Consulte la Sección 8.16.6 para obtener comentarios adicionales.

Ejemplos:

- incluye 3, no incluye 7 e incluye todos los puntos intermediosSELECCIONE'[3,7)'::int4range;-- does not include either 3 or 7, but includes all points in betweenSELECT'(3,7)'::int4range;-- includes only the single point 4SELECT'[4,4]':: int4range;- no incluye puntos (y se normalizará a 'vacío')SELECCIONE'[4,4)'::int4range;

8.17.6. Constructing Ranges

Each range type has a constructor function with the same name as the range type. Using the constructor function is frequently more convenient than writing a range literal constant, since it avoids the need for extra quoting of the bound values. The constructor function accepts two or three arguments. The two-argument form constructs a range in standard form (lower bound inclusive, upper bound exclusive), while the three-argument form constructs a range with bounds of the form specified by the third argument. The third argument must be one of the strings “()”, “(]“, “[)”, or “[]“. Por ejemplo:

-- The full form is: lower bound, upper bound, and text argument indicating-- inclusivity/exclusivity of bounds.SELECT numrange(1.0,14.0,'(]');-- If the third argument is omitted, '[)' is assumed.SELECT numrange(1.0,14.0);-- Although '(]' is specified here, on display the value will be converted to-- canonical form, since int8range is a discrete range type (see below).SELECT int8range(1,14,'(]');-- Using NULL for either bound causes the range to be unbounded on that side.SELECT numrange(NULL,2.2);

8.17.7. Tipos de rango discreto

Un rango discreto es aquel cuyo tipo de elemento tiene un bien definido “paso“, tal como integer o date. En estos tipos se puede decir que dos elementos son adyacentes, cuando no hay valores válidos entre ellos. Esto contrasta con los rangos continuos, donde siempre (o casi siempre) es posible identificar otros valores de elementos entre dos valores dados. Por ejemplo, un rango sobre el numeric el tipo es continuo, al igual que un rango sobre timestamp. (Aunque timestamp tiene una precisión limitada, por lo que teóricamente podría tratarse como discreto, es mejor considerarlo continuo ya que el tamaño del paso normalmente no es de interés).

Otra forma de pensar en un tipo de rango discreto es que existe una idea clara de un “Siguiente“ o “anterior“ valor para cada valor de elemento. Sabiendo eso, es posible convertir entre representaciones inclusivas y exclusivas de los límites de un rango, eligiendo el valor del elemento anterior o siguiente en lugar del valor dado originalmente. Por ejemplo, en un tipo de rango de enteros [4,8] y (3,9) denotar el mismo conjunto de valores; pero esto no sería así para un rango sobre numérico.

Un tipo de rango discreto debe tener un canonicalización función que es consciente del tamaño de paso deseado para el tipo de elemento. La función de canonicalización se encarga de convertir valores equivalentes del tipo de rango para tener representaciones idénticas, en particular límites consistentemente inclusivos o exclusivos. Si no se especifica una función de canonicalización, los rangos con formato diferente siempre se tratarán como desiguales, aunque en realidad representen el mismo conjunto de valores.

Los tipos de rango incorporado int4range, int8range, y daterange todos usan una forma canónica que incluye el límite inferior y excluye el límite superior; es decir, [). User-defined range types can use other conventions, however.

8.17.8. Defining New Range Types

Users can define their own range types. The most common reason to do this is to use ranges over subtypes not provided among the built-in range types. For example, to define a new range type of subtype float8:

CREATETYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);SELECT'[1.234, 5.678]':: floatrange;

Porque float8 no tiene sentido “paso“, no definimos una función de canonicalización en este ejemplo.

Definir su propio tipo de rango también le permite especificar una clase o colación de operador de árbol B de subtipo diferente para usar, para cambiar el orden de clasificación que determina qué valores caen en un rango dado.

Si se considera que el subtipo tiene valores discretos en lugar de continuos, el CREATE TYPE El comando debe especificar un canonical función. La función de canonicalización toma un valor de rango de entrada y debe devolver un valor de rango equivalente que puede tener diferentes límites y formato. La salida canónica para dos rangos que representan el mismo conjunto de valores, por ejemplo, los rangos de números enteros [1, 7] y [1, 8), must be identical. It doesn’t matter which representation you choose to be the canonical one, so long as two equivalent values with different formattings are always mapped to the same value with the same formatting. In addition to adjusting the inclusive/exclusive bounds format, a canonicalization function might round off boundary values, in case the desired step size is larger than what the subtype is capable of storing. For instance, a range type over timestamp could be defined to have a step size of an hour, in which case the canonicalization function would need to round off bounds that weren’t a multiple of an hour, or perhaps throw an error instead.

In addition, any range type that is meant to be used with GiST or SP-GiST indexes should define a subtype difference, or subtype_diff, function. (The index will still work without subtype_diff, but it is likely to be considerably less efficient than if a difference function is provided.) The subtype difference function takes two input values of the subtype, and returns their difference (i.e., X minus Y) represented as a float8 value. In our example above, the function float8mi that underlies the regular float8 minus operator can be used; but for any other subtype, some type conversion would be necessary. Some creative thought about how to represent differences as numbers might be needed, too. To the greatest extent possible, the subtype_diff function should agree with the sort ordering implied by the selected operator class and collation; that is, its result should be positive whenever its first argument is greater than its second according to the sort ordering.

A less-oversimplified example of a subtype_diff function is:

CREATEFUNCTION time_subtype_diff(x time, y time)RETURNS float8 AS'SELECT EXTRACT(EPOCH FROM (x - y))'LANGUAGEsql STRICT IMMUTABLE;CREATETYPE timerange AS RANGE (
    subtype =time,
    subtype_diff = time_subtype_diff
);SELECT'[11:10, 23:00]'::intervalo de tiempo;

Consulte CREAR TIPO para obtener más información sobre cómo crear tipos de rango.

8.17.9. Indexación

Se pueden crear índices GiST y SP-GiST para columnas de tabla de tipos de rango. Por ejemplo, para crear un índice GiST:

CREATEINDEX reservation_idx ON reservation USING GIST (during);

Un índice GiST o SP-GiST puede acelerar las consultas que involucran estos operadores de rango: =, &&, <@, @>, <<, >>, -|-, &<, y &> (consulte la Tabla 9.53 para obtener más información).

Además, se pueden crear índices hash y de árbol B para columnas de tabla de tipos de rango. Para estos tipos de índices, básicamente la única operación de rango útil es la igualdad. Hay un orden de clasificación de árbol B definido para valores de rango, con los correspondientes < y > operadores, pero el orden es bastante arbitrario y no suele ser útil en el mundo real. El soporte de hash y árbol B de los tipos de rango está destinado principalmente a permitir la clasificación y el hash internamente en las consultas, en lugar de la creación de índices reales.

8.17.10. Restricciones en rangos

Tiempo UNIQUE es una restricción natural para los valores escalares, por lo general no es adecuada para los tipos de rango. En cambio, una restricción de exclusión suele ser más apropiada (consulte CREAR TABLA ... RESTRICCIÓN ... EXCLUIR). Las restricciones de exclusión permiten la especificación de restricciones como "no superpuesto" en un tipo de rango. Por ejemplo:

CREATETABLE reservation (
    during tsrange,
    EXCLUDE USING GIST (during WITH&&));

Esa restricción evitará que existan valores superpuestos en la tabla al mismo tiempo:

INSERTINTO reservation VALUES('[2010-01-01 11:30, 2010-01-01 15:00)');INSERT01INSERTINTO reservation VALUES('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting keyvalue 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")).

Puedes usar el btree_gist extensión para definir restricciones de exclusión en tipos de datos escalares simples, que luego se pueden combinar con exclusiones de rango para una máxima flexibilidad. Por ejemplo, después btree_gist está instalado, la siguiente restricción rechazará los rangos superpuestos solo si los números de la sala de reuniones son iguales:

CREATE EXTENSION btree_gist;CREATETABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH=, during WITH&&));INSERTINTO room_reservation VALUES('123A','[2010-01-01 14:00, 2010-01-01 15:00)');INSERT01INSERTINTO room_reservation VALUES('123A','[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR:  conflicting keyvalue 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")).INSERTINTO room_reservation VALUES('123B','[2010-01-01 14:30, 2010-01-01 15:30)');INSERT01