PostgreSQL - Tipo de datos

En este capítulo, analizaremos los tipos de datos utilizados en PostgreSQL. Al crear la tabla, para cada columna, especifica un tipo de datos, es decir, qué tipo de datos desea almacenar en los campos de la tabla.

Esto permite varios beneficios:

  • Consistency - Las operaciones contra columnas del mismo tipo de datos dan resultados consistentes y suelen ser las más rápidas.

  • Validation - El uso adecuado de los tipos de datos implica la validación del formato de los datos y el rechazo de los datos fuera del alcance del tipo de datos.

  • Compactness - Como una columna puede almacenar un solo tipo de valor, se almacena de forma compacta.

  • Performance- El uso adecuado de los tipos de datos proporciona el almacenamiento de datos más eficiente. Los valores almacenados se pueden procesar rápidamente, lo que mejora el rendimiento.

PostgreSQL admite un amplio conjunto de tipos de datos. Además, los usuarios pueden crear su propio tipo de datos personalizados usando el comando CREATE TYPE SQL. Hay diferentes categorías de tipos de datos en PostgreSQL. Se comentan a continuación.

Tipos numéricos

Los tipos numéricos constan de enteros de dos, cuatro y ocho bytes, números de coma flotante de cuatro y ocho bytes y decimales de precisión seleccionable. La siguiente tabla enumera los tipos disponibles.

Nombre Tamaño de almacenamiento Descripción Rango
pequeño 2 bytes entero de rango pequeño -32768 al +32767
entero 4 bytes elección típica para entero -2147483648 al +2147483647
Empezando 8 bytes entero de rango grande -9223372036854775808 al 9223372036854775807
decimal variable precisión especificada por el usuario, exacta hasta 131072 dígitos antes del punto decimal; hasta 16383 dígitos después del punto decimal
numérico variable precisión especificada por el usuario, exacta hasta 131072 dígitos antes del punto decimal; hasta 16383 dígitos después del punto decimal
real 4 bytes precisión variable, inexacta Precisión de 6 dígitos decimales
Precisión doble 8 bytes precisión variable, inexacta Precisión de 15 dígitos decimales
pequeña serie 2 bytes pequeño entero autoincrementante 1 hasta 32767
de serie 4 bytes entero autoincrementante 1 al 2147483647
bigserial 8 bytes entero grande autoincrementante 1 al 9223372036854775807

Tipos monetarios

El tipo de dinero almacena una cantidad de moneda con una precisión fraccionaria fija. Los valores de los tipos de datos numérico, int y bigint se pueden convertir en dinero . No se recomienda el uso de números de coma flotante para manejar dinero debido a la posibilidad de errores de redondeo.

Nombre Tamaño de almacenamiento Descripción Rango
dinero 8 bytes cantidad de moneda -92233720368547758.08 al +92233720368547758.07

Tipos de caracteres

La tabla que se proporciona a continuación enumera los tipos de caracteres de uso general disponibles en PostgreSQL.

S. No. Nombre y descripción
1

character varying(n), varchar(n)

longitud variable con límite
2

character(n), char(n)

de longitud fija, en blanco acolchado
3

text

longitud ilimitada variable

Tipos de datos binarios

El tipo de datos bytea permite el almacenamiento de cadenas binarias como se muestra en la siguiente tabla.

Nombre Tamaño de almacenamiento Descripción
bytea 1 o 4 bytes más la cadena binaria real cadena binaria de longitud variable

Tipos de fecha / hora

PostgreSQL admite un conjunto completo de tipos de fecha y hora de SQL, como se muestra en la tabla siguiente. Las fechas se cuentan según el calendario gregoriano. Aquí, todos los tipos tienen resolución de1 microsecond / 14 digits excepto date tipo, cuya resolución es day.

Nombre Tamaño de almacenamiento Descripción Bajo valor Alto valor
marca de tiempo [(p)] [sin zona horaria] 8 bytes tanto la fecha como la hora (sin zona horaria) 4713 a. C. 294276 AD
TIMESTAMPTZ 8 bytes tanto la fecha como la hora, con la zona horaria 4713 a. C. 294276 AD
fecha 4 bytes fecha (sin hora del día) 4713 a. C. 5874897 AD
hora [(p)] [sin zona horaria] 8 bytes hora del día (sin fecha) 00:00:00 24:00:00
hora [(p)] con zona horaria 12 bytes solo horas del día, con zona horaria 00: 00: 00 + 1459 24: 00: 00-1459
intervalo [campos] [(p)] 12 bytes intervalo de tiempo -178000000 años 178000000 años

Tipo booleano

PostgreSQL proporciona el tipo de SQL estándar Boolean. El tipo de datos booleano puede tener los estados verdadero , falso y un tercer estado, desconocido , que está representado por el valor nulo de SQL.

Nombre Tamaño de almacenamiento Descripción
booleano 1 byte estado de verdadero o falso

Tipo enumerado

Los tipos enumerados (enum) son tipos de datos que comprenden un conjunto de valores estático y ordenado. Son equivalentes a los tipos de enumeración admitidos en varios lenguajes de programación.

A diferencia de otros tipos, los tipos enumerados deben crearse mediante el comando CREATE TYPE. Este tipo se utiliza para almacenar un conjunto de valores estático y ordenado. Por ejemplo, direcciones de la brújula, es decir, NORTE, SUR, ESTE y OESTE o días de la semana como se muestra a continuación:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

Enumerado, una vez creado, se puede utilizar como cualquier otro tipo.

Tipo geométrico

Los tipos de datos geométricos representan objetos espaciales bidimensionales. El tipo más fundamental, el punto, forma la base de todos los demás tipos.

Nombre Tamaño de almacenamiento Representación Descripción
punto 16 bytes Punto en un avión (x, y)
línea 32 bytes Línea infinita (no implementada completamente) ((x1, y1), (x2, y2))
lseg 32 bytes Segmento de línea finita ((x1, y1), (x2, y2))
caja 32 bytes Caja rectangular ((x1, y1), (x2, y2))
camino 16 + 16n bytes Camino cerrado (similar al polígono) ((x1, y1), ...)
camino 16 + 16n bytes Camino abierto [(x1, y1), ...]
polígono 40 + 16n Polígono (similar al camino cerrado) ((x1, y1), ...)
circulo 24 bytes Circulo <(x, y), r> (punto central y radio)

Tipo de dirección de red

PostgreSQL ofrece tipos de datos para almacenar direcciones IPv4, IPv6 y MAC. Es mejor usar estos tipos en lugar de tipos de texto sin formato para almacenar direcciones de red, porque estos tipos ofrecen verificación de errores de entrada y operadores y funciones especializados.

Nombre Tamaño de almacenamiento Descripción
cidr 7 o 19 bytes Redes IPv4 e IPv6
inet 7 o 19 bytes Redes y hosts IPv4 e IPv6
macaddr 6 bytes Direcciones MAC

Tipo de cadena de bits

Los tipos de cadenas de bits se utilizan para almacenar máscaras de bits. Son 0 o 1. Hay dos tipos de bits SQL:bit(n) y bit varying(n), donde n es un número entero positivo.

Tipo de búsqueda de texto

Este tipo admite la búsqueda de texto completo, que es la actividad de buscar a través de una colección de documentos en lenguaje natural para ubicar los que mejor coinciden con una consulta. Hay dos tipos de datos para esto:

S. No. Nombre y descripción
1

tsvector

Esta es una lista ordenada de palabras distintas que se han normalizado para fusionar diferentes variantes de la misma palabra, llamadas "lexemas".
2

tsquery

Esto almacena los lexemas que se van a buscar y los combina respetando los operadores booleanos & (Y), | (O y ! (NO). Se pueden utilizar paréntesis para imponer la agrupación de los operadores.

Tipo de UUID

Un UUID (Universal Unique Identifiers) se escribe como una secuencia de dígitos hexadecimales en minúsculas, en varios grupos separados por guiones, específicamente un grupo de ocho dígitos, seguido de tres grupos de cuatro dígitos, seguido de un grupo de 12 dígitos, para un total de 32 dígitos que representan los 128 bits.

Un ejemplo de UUID es: 550e8400-e29b-41d4-a716-446655440000

Tipo XML

El tipo de datos XML se puede utilizar para almacenar datos XML. Para almacenar datos XML, primero debe crear valores XML utilizando la función xmlparse de la siguiente manera:

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Tipo JSON

El tipo de datos json se puede utilizar para almacenar datos JSON (notación de objetos JavaScript). Dichos datos también se pueden almacenar como texto , pero el tipo de datos json tiene la ventaja de verificar que cada valor almacenado sea un valor JSON válido. También hay funciones de soporte relacionadas disponibles, que se pueden usar directamente para manejar el tipo de datos JSON de la siguiente manera.

Ejemplo Resultado de ejemplo
array_to_json ('{{1,5}, {99,100}}' :: int []) [[1,5], [99,100]]
fila_a_json (fila (1, 'foo')) {"f1": 1, "f2": "foo"}

Tipo de matriz

PostgreSQL brinda la oportunidad de definir una columna de una tabla como una matriz multidimensional de longitud variable. Se pueden crear matrices de cualquier tipo de base, tipo de enumeración o tipo compuesto integrado o definido por el usuario.

Declaración de matrices

El tipo de matriz se puede declarar como

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

o utilizando la palabra clave "ARRAY" como

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

Insertar valores

Los valores de matriz se pueden insertar como una constante literal, encerrando los valores del elemento entre llaves y separándolos por comas. A continuación se muestra un ejemplo:

INSERT INTO monthly_savings 
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

Acceso a matrices

A continuación, se muestra un ejemplo para acceder a las matrices. El comando dado a continuación seleccionará a las personas cuyos ahorros son más en el segundo trimestre que en el cuarto trimestre.

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Modificar matrices

Un ejemplo de modificación de matrices se muestra a continuación.

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

o usando la sintaxis de expresión ARRAY -

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

Buscando matrices

Un ejemplo de búsqueda de matrices se muestra a continuación.

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

Si se conoce el tamaño de la matriz, se puede utilizar el método de búsqueda indicado anteriormente. De lo contrario, el siguiente ejemplo muestra cómo buscar cuando no se conoce el tamaño.

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

Tipos compuestos

Este tipo representa una lista de nombres de campo y sus tipos de datos, es decir, estructura de una fila o registro de una tabla.

Declaración de tipos compuestos

El siguiente ejemplo muestra cómo declarar un tipo compuesto

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
);

Este tipo de datos se puede utilizar en las tablas de creación como se muestra a continuación:

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Entrada de valor compuesto

Los valores compuestos se pueden insertar como una constante literal, encerrando los valores de campo entre paréntesis y separándolos por comas. A continuación se muestra un ejemplo:

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

Esto es válido para el elemento de inventario definido anteriormente. La palabra clave ROW es realmente opcional siempre que tenga más de un campo en la expresión.

Acceso a tipos compuestos

Para acceder a un campo de una columna compuesta, utilice un punto seguido del nombre del campo, de forma muy similar a seleccionar un campo de un nombre de tabla. Por ejemplo, para seleccionar algunos subcampos de nuestra tabla de ejemplo on_hand, la consulta sería como se muestra a continuación:

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

Incluso puede usar el nombre de la tabla también (por ejemplo, en una consulta de múltiples tablas), así:

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

Tipos de rango

Los tipos de rango representan tipos de datos que utilizan un rango de datos. El tipo de rango puede ser rangos discretos (por ejemplo, todos los valores enteros de 1 a 10) o rangos continuos (por ejemplo, cualquier momento entre las 10:00 am y las 11:00 am).

Los tipos de rango integrados disponibles incluyen los siguientes rangos:

  • int4range - Rango de entero

  • int8range - Rango de bigint

  • numrange - Rango de numérico

  • tsrange - Rango de marca de tiempo sin zona horaria

  • tstzrange - Rango de marca de tiempo con zona horaria

  • daterange - Rango de fecha

Se pueden crear tipos de rango personalizados para que estén disponibles nuevos tipos de rangos, como rangos de direcciones IP usando el tipo inet como base, o rangos flotantes usando el tipo de datos flotante como base.

Los tipos de rango admiten límites de rango inclusivos y exclusivos utilizando los caracteres [] y (), respectivamente. Por ejemplo, '[4,9)' representa todos los números enteros comenzando desde e incluyendo 4 hasta pero sin incluir 9.

Tipos de identificadores de objetos

PostgreSQL utiliza internamente identificadores de objetos (OID) como claves principales para varias tablas del sistema. Si se especifica WITH OIDS o la variable de configuración default_with_oids está habilitada, solo entonces, en tales casos, los OID se agregan a las tablas creadas por el usuario. La siguiente tabla enumera varios tipos de alias. Los tipos de alias de OID no tienen operaciones propias, excepto las rutinas especializadas de entrada y salida.

Nombre Referencias Descripción Ejemplo de valor
oid ninguna identificador de objeto numérico 564182
regproc pg_proc nombre de la función suma
reglamento pg_proc función con tipos de argumentos suma (int4)
regoper pg_operator nombre del operador +
regoperador pg_operator operador con tipos de argumentos * (entero, entero) o - (NINGUNO, entero)
regclass pg_class nombre de la relación pg_type
regtipo pg_type nombre del tipo de datos entero
regconfig pg_ts_config configuración de búsqueda de texto Inglés
regdiccionario pg_ts_dict diccionario de búsqueda de texto simple

Pseudo tipos

El sistema de tipos de PostgreSQL contiene una serie de entradas de propósito especial que se denominan colectivamente pseudo-tipos. Un pseudo-tipo no se puede usar como un tipo de datos de columna, pero se puede usar para declarar el argumento de una función o el tipo de resultado.

La tabla que se proporciona a continuación enumera los pseudo-tipos existentes.

S. No. Nombre y descripción
1

any

Indica que una función acepta cualquier tipo de datos de entrada.
2

anyelement

Indica que una función acepta cualquier tipo de datos.
3

anyarray

Indica que una función acepta cualquier tipo de datos de matriz.
4

anynonarray

Indica que una función acepta cualquier tipo de datos que no sea de matriz.
5

anyenum

Indica que una función acepta cualquier tipo de datos de enumeración.
6

anyrange

Indica que una función acepta cualquier tipo de datos de rango.
7

cstring

Indica que una función acepta o devuelve una cadena C terminada en nulo.
8

internal

Indica que una función acepta o devuelve un tipo de datos interno del servidor.
9

language_handler

Se declara que un controlador de llamadas de lenguaje procedimental devuelve language_handler.
10

fdw_handler

Se declara que un controlador de contenedor de datos externos devuelve fdw_handler.
11

record

Identifica una función que devuelve un tipo de fila no especificado.
12

trigger

Se declara que una función de disparo devuelve un disparador.
13

void

Indica que una función no devuelve ningún valor.