Tipos de datos de PostgreSQL

En este capítulo, discutiremos los tipos de datos de PostgreSQL, que son los tipos de datos que establecemos para cada campo al crear una tabla.

Los beneficios de establecer el tipo de datos:

PostgreSQL ofrece una amplia variedad de tipos de datos. Los usuarios pueden usar la orden CREATE TYPE para crear nuevos tipos de datos en la base de datos. Hay muchos tipos de datos en PostgreSQL, y a continuación se explicarán en detalle.

Tipos de datos numéricos

Los tipos de datos numéricos se componen de 2 Bytes,4 O bytes 8 Enteros de bytes y 4 O bytes 8 Número de bytes de coma flotante y número decimal con precisión opcional

La siguiente tabla enumera los tipos de datos numéricos disponibles.

Tipos monetarios

El tipo money almacena cantidades monetarias con precisión decimal fija.

Los valores de los tipos numeric, int y bigint pueden convertirse a money, no se recomienda usar números de coma flotante para manejar tipos monetarios, ya que existe la posibilidad de errores de redondeo.

Tipos de caracteres

La siguiente tabla enumera los tipos de caracteres admitidos por PostgreSQL:

Fecha/Tipos de tiempo

La siguiente tabla enumera los tipos de datos de fecha y hora admitidos por PostgreSQL.

Tipo booleano

PostgreSQL admite el tipo de datos booleano estándar.

boolean tiene dos estados: "true" (verdadero) o "false" (falso), y un tercer estado "unknown" (desconocido), representado por NULL.

tipos de enumeración

El tipo de enumeración es un tipo de datos que es una colección ordenada de valores estáticos y valores.

Los tipos de enumeración en PostgreSQL son similares a los tipos enum en el lenguaje C.

Diferente de otros tipos, los tipos de enumeración necesitan ser creados utilizando el comando CREATE TYPE.

CREATE　TYPE　mood　AS　ENUM　('triste',　'ok',　'feliz');

La creación de días de la semana se muestra a continuación:

CREATE　TYPE　week　AS　ENUM　('Lun',　'Mar',　'Mie',　'Jue',　'Vie',　'Sab',　'Dom');

Al igual que otros tipos, una vez creada, el tipo de enumeración se puede usar en definiciones de tablas y funciones.

CREATE　TYPE　mood　AS　ENUM　('triste',　'ok',　'feliz');
CREATE　TABLE　person　(
　　　　nombre text,
　　　　current_mood　mood
);
INSERT　INTO　person　VALUES　('Moe',　'feliz');
SELECT　*　FROM　person　WHERE　current_mood　=　'feliz';
　name　|　current_mood　
------+--------------
　Moe　　|　feliz
(1　row)

tipos geométricos

Los tipos de datos geométricos representan objetos planos bidimensionales.

La siguiente tabla enumera los tipos de datos geométricos soportados por PostgreSQL.

El tipo más básico: punto. Es la base para otros tipos.

tipos de datos de dirección de red

PostgreSQL proporciona tipos de datos para almacenar IPv4 y IPv6 y el tipo de datos de dirección MAC.

Es mejor almacenar direcciones de red utilizando estos tipos de datos en lugar de tipos de texto puros, ya que estos tipos proporcionan verificación de errores de entrada y operaciones y funciones especiales.

Al ordenar datos de tipo inet o cidr, IPv4 La dirección siempre está precedida por IPv6 Antes de la dirección, incluyendo las que están encapsuladas o mapeadas en IPv6 La dirección IPv4 dirección, por ejemplo::10.2.3.4 o ::ffff:10.4.3.2.

tipo de secuencia de bits

Una secuencia de bits es una 1 y la cadena de 0.

Los datos del tipo bit deben coincidir exactamente con la longitud n, intentar almacenar datos más cortos o más largos es incorrecto. Los datos del tipo bit varying son del tipo variable más largo que n; las cadenas más largas serán rechazadas. Escribir un bit sin longitud es equivalente a bit(1) significa que no hay límite de longitud.

tipos de búsqueda de texto

La búsqueda completa es encontrar los documentos que coinciden con una consulta a partir de una colección de documentos de lenguaje natural.

PostgreSQL ofrece dos tipos de datos para soportar la búsqueda completa:

Tipos de datos UUID

el tipo de datos uuid se utiliza para almacenar RFC 4122, ISO/IEF 9834-82005 y los estándares relacionados que definen el identificador universal único (UUID). (Algunos sistemas consideran que este tipo de datos es un identificador universal único o GUID.). Este identificador es un valor generado por algoritmo 128 identificador que hace que sea imposible que este identificador sea idéntico a otro generado de la misma manera en otros módulos conocidos. Por lo tanto, para los sistemas distribuidos, este identificador ofrece una mejor garantía de unicidad que una secuencia, ya que la secuencia solo garantiza la unicidad en una única base de datos.

Un UUID se escribe como una secuencia de números hexadecimales en minúsculas, dividida en varios grupos por caracteres de separación, especialmente un grupo8números+3grupos4números+un grupo12números, en total 32 números representan 128 Un número representativo, un ejemplo de UUID de este estándar es el siguiente:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

Tipos de datos XML

Los tipos de datos xml se pueden usar para almacenar datos XML. La ventaja de almacenar datos XML en el tipo de datos text es que permite verificar la的良好性 de los valores de entrada, y también admite la verificación de seguridad de tipo de las funciones. Para usar este tipo de datos, es necesario usar configure en tiempo de compilación. --con-libxml.

xml puede almacenar documentos bien formados definidos por el estándar XML, así como por Decl de XML? contenido El fragmento "contenido" definido, lo que básicamente significa que el fragmento de contenido puede tener varios elementos de nivel superior o nodos de caracteres. La expresión xmlvalue IS DOCUMENT se puede usar para determinar si un valor XML específico es un archivo completo o un fragmento de contenido. xmlvalue IS DOCUMENT

Crear valor XML

Usar la función xmlparse: para generar valores de tipo XML a partir de datos de caracteres:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><título>Manual</título><capítulo>...<//capítulo></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

Tipo JSON

El tipo de datos JSON se puede usar para almacenar datos JSON (JavaScript Object Notation), estos datos también se pueden almacenar como texto, pero el tipo de datos JSON es más beneficioso para verificar que cada valor almacenado sea un valor JSON válido.

Además, hay funciones relacionadas para manejar datos JSON:

Tipos de array

PostgreSQL permite definir campos como arrays multidimensionales de longitud variable.

El tipo de array puede ser cualquier tipo básico o tipo definido por el usuario, tipo enumerado o tipo compuesto.

Declaración de array

Al crear la tabla, podemos declarar arrays de la siguiente manera:

CREATE TABLE sal_emp (
　　　　name text,
　　　　pagar_por_trimestre integer[],
　　　　texto_horario text[][]
);

pagar_por_trimestre es un array unidimensional de enteros, schedule es un array bidimensional de texto.

También podemos usar la palabra clave "ARRAY", como se muestra a continuación:

CREATE TABLE sal_emp (
　　　nombre text,
　　　pagar_por_trimestre integer ARRAY[4],
　　　texto_horario[][]
);

Valores de inserción

Los valores de inserción se utilizan entre corchetes {} y los elementos se separan con comas dentro de {}:

INSERT INTO sal_emp
　　　　VALUES ('Bill',
　　　　'{10000,　10000,　10000,　10000}',
　　　　'{{"reunión", "almuerzo"}, {{"entrenamiento", "presentación"}}}')
INSERT INTO sal_emp
　　　　VALUES　('Carol',
　　　　'{20000,　25000,　25000,　25000}',
　　　　'{{"breakfast",　"consulting"},　{"meeting",　"lunch"}}');

访问数组

现在我们可以在这个表上运行一些查询。

首先，我们演示如何访问数组的一个元素。 这个查询检索在第二季度薪水变化的雇员名：

SELECT　name　FROM　sal_emp　WHERE　pay_by_quarter[1]　<>　pay_by_quarter[2];
　name
-------
　Carol
(1　row)

数组的下标数字是写在方括弧内的。

修改数组

我们可以对数组的值进行修改：

UPDATE　sal_emp　SET　pay_by_quarter　=　'{25000,25000,27000,27000}'
　　　　WHERE　name　=　'Carol';

或者使用 ARRAY 构造器语法：

UPDATE　sal_emp　SET　pay_by_quarter　=　ARRAY[25000,25000,27000,27000]
　　　　WHERE　name　=　'Carol';

数组中检索

要搜索一个数组中的数值，你必须检查该数组的每一个值。

比如：

SELECT　*　FROM　sal_emp　WHERE　pay_by_quarter[1]　=　10000　OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[2]　=　10000　OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[3]　=　10000　OR
　　　　　　　　　　　　　　　　　　　　　　　　　　　　pay_by_quarter[4]　=　10000;

另外，你可以用下面的语句找出数组中所有元素值都等于 10000 的行：

SELECT　*　FROM　sal_emp　WHERE　10000　=　ALL　(pay_by_quarter);

或者，可以使用 generate_subscripts 函数。例如：

SELECT　*　FROM
　　　(SELECT　pay_by_quarter,
　　　　　　　　　　　generate_subscripts(pay_by_quarter,　1)　AS　s
　　　　　　FROM　sal_emp)　AS　foo
　WHERE　pay_by_quarter[s]　=　10000;

复合类型

复合类型表示一行或者一条记录的结构； 它实际上只是一个字段名和它们的数据类型的列表。PostgreSQL 允许像简单数据类型那样使用复合类型。比如，一个表的某个字段可以声明为一个复合类型。

声明复合类型

下面是两个定义复合类型的简单实例：

CREATE　TYPE　complex　AS　(
　　　　r　　　　　　　double　precision,
　　　　i double precision
);
CREATE TYPE inventory_item AS (
　　　　name text,
　　　　supplier_id integer,
　　　　price numeric
);

La sintaxis es similar a CREATE TABLE, solo se pueden declarar nombres de campos y tipos.

Definimos el tipo, luego podemos usarlo para crear tablas:

CREATE TABLE on_hand (
　　　　item inventory_item,
　　　　count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice',　42,　1.99),　1000);

Entrada de valor de tipo compuesto

Para escribir valores de tipo compuesto como constantes de texto, encierra los valores de campo entre corchetes y separalos con comas. Puedes poner comillas dobles alrededor de cualquier valor de campo, si el valor contiene comas o corchetes, debes encerrarlos con comillas dobles.

El formato general de constante de tipo compuesto es el siguiente:

' ( val1　, val2　, ... )'

Un ejemplo es:

'("fuzzy dice",42,1.99)'

Acceder a tipo compuesto

Para acceder a un dominio de campo de tipo compuesto, escribimos un punto y el nombre del dominio, muy similar a seleccionar un campo de un nombre de tabla. En realidad, debido a que es demasiado similar a seleccionar un campo de un nombre de tabla, a menudo necesitamos usar corchetes para evitar la confusión del analizador. Por ejemplo, podrías necesitar seleccionar algunos subdominios de la tabla de instancias on_hand, como se muestra a continuación:

SELECT item.name FROM on_hand WHERE item.price >　9.99;

Esto no funcionará, porque según la sintaxis de SQL, item se selecciona de un nombre de tabla, no de un nombre de campo. Debes escribir como se muestra a continuación:

SELECT (item).name FROM on_hand WHERE (item).price >　9.99;

O si también necesitas usar nombres de tablas (por ejemplo, en una consulta de múltiples tablas), escribe así:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price >　9.99;

Ahora, el objeto de corchetes se analiza correctamente como una referencia al campo item, luego se puede seleccionar el subdominio.

Tipo de rango

El tipo de datos de rango representa los valores de un tipo de elemento en un rango determinado.

Por ejemplo, el rango de timestamp puede ser utilizado para representar el rango de tiempo reservado de una sala de reuniones.

Los tipos de rango integrados en PostgreSQL son:

• int4range —rango de integer

• int8range —rango de bigint

• numrange —rango numérico

• tsrange —rango de timestamp sin zona horaria

• tstzrange —rango de timestamp con zona horaria

• daterange —rango de fecha

Además, puedes definir tu propio tipo de rango.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
　　　　(1108'[2010-01-01　1430,　2010-01-01　1530)');
--　Incluye
SELECT int4range(10,　20) @>　3;
--　Superposición
SELECT numrange(11.1,　22.2) && numrange(20.0,　30.0);
--　Extraer el límite superior
SELECT upper(int8range(15,　25));
--　Calcular la intersección
SELECT int4range(10,　20)　*　int4range(15,　25);
--　¿El rango está vacío?
SELECT isempty(numrange(1,　5));

La entrada de valores de rango debe seguir el siguiente formato:

(límite inferior, límite superior)
(límite inferior, límite superior]
[límite inferior, límite superior)
[límite inferior, límite superior]
Vacio

Los paréntesis redondos o cuadrados muestran si los límites inferior y superior son inclusivos o no. Nota: el formato final es vacío, que representa un rango vacío (un rango sin valores).

--　Incluye3No incluye7y incluye todos los puntos entre ambos
SELECT '[3,7)::int4range;
--　No incluye3y7pero incluye todos los puntos entre ambos
SELECT '('3,7)::int4range;
--　Sólo incluye un valor4
SELECT '[4,4]'::int4range;
--　No incluye puntos (estandarizados como 'vacio')
SELECT '[4,4)::int4range;

Tipo de identificador de objeto

PostgreSQL utiliza internamente el identificador de objeto (OID) como clave principal de varias tablas del sistema.

Además, el sistema no agregará un campo de identificador de objeto OID (a menos que se declare WITH OIDS en el momento de crear la tabla o se configure el parámetro de configuración default_with_oids como activado). El tipo oid representa un identificador de objeto. Además, oid tiene varios alias: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig y regdictionary.

Tipos de pseudo

El sistema de tipos de PostgreSQL contiene una serie de entradas de uso especial, que se denominan tipos de pseudo. Los tipos de pseudo no pueden ser utilizados como tipos de datos de campo, pero pueden ser utilizados para declarar el tipo de parámetro o resultado de una función. Los tipos de pseudo son muy útiles cuando una función no solo acepta y devuelve algún tipo de tipo de datos SQL.

A continuación se lista todos los tipos de pseudo: