PostgreSQL – Tipo de datos

En este capítulo, vamos a discutir sobre los tipos de datos utilizados en PostgreSQL. Mientras que la creación de la tabla, para cada columna, se especifica un tipo de datos, es decir, qué tipo de datos desea almacenar en los campos de la tabla.

Esto permite varios beneficios –

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

• Validación – 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 ámbito del tipo de datos.

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

• Rendimiento – El uso adecuado de los tipos de datos proporciona el almacenamiento más eficiente de los datos. Los valores almacenados pueden ser procesados rápidamente, lo que mejora el rendimiento.

PostgreSQL soporta un amplio conjunto de Tipos de Datos. Además, los usuarios pueden crear su propio tipo de datos personalizado utilizando el comando CREATE TYPE SQL. Hay diferentes categorías de tipos de datos en PostgreSQL. Se discuten a continuación.

Tipos numéricos

Los tipos numéricos consisten en enteros de dos bytes, cuatro bytes y ocho bytes, números de punto flotante de cuatro bytes 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ñoint	2 bytes	pequeño-rango integer	-32768 a +32767
integer	4 bytes	elección típica para integer	-2147483648 a +2147483647
bigint	8 bytes	entero de gran rango	-9223372036854775808 a 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	6 dígitos decimales precisión
doble precisión	8 bytes	precisión variable,inexacto	15 dígitos decimales de precisión
serial pequeño	2 bytes	pequeño autoincremento integer	1 a 32767
serial	4 bytes	autoincrementante integer	1 a 2147483647
bigserial	8 bytes	entero grande autoincrementable	1 a 9223372036854775807

Tipos monetarios

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

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

Tipos de caracteres

La tabla que se ofrece a continuación enumera los tipos de caracteres de propósito general disponibles en PostgreSQL.

S. No.	Nombre &Descripción
1	caracteres variables(n), varchar(n) longitud variable con límite
2	carácter(n), char(n) longitud fija, blank padded
3	text variable de longitud ilimitada

Tipos de datos binarios

El tipo de datos bytea permite almacenar cadenas binarias como en la siguiente tabla.

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

Tipos de fecha/hora

PostgreSQL soporta 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 una resolución de 1 microsegundo / 14 dígitos excepto el tipo de fecha, cuya resolución es de día.

Nombre	Tamaño de almacenamiento	Descripción	Valor bajo	Valor alto
timestamp	8 bytes	tanto la fecha como la hora (sin zona horaria)	4713 AC	294276 AD
TIMESTAMPTZ	8 bytes	tanto la fecha como la hora, con zona horaria	4713 AC	294276 AD
fecha	4 bytes	fecha (sin hora del día)	4713 a.C.	5874897 d.C.
hora	8 bytes	hora del día (sin fecha)	00:00:00	24:00:00
hora con huso horario	12 bytes	horas del día sólo, con huso horario	00:00:00+1459	24:00:00-1459
intervalo	12 bytes	intervalo de tiempo	-178000000 años	178000000 años

Tipo Booleano

PostgreSQL proporciona el tipo SQL estándar Booleano. El tipo de datos booleano puede tener los estados verdadero, falso y un tercer estado, desconocido, que se representa con 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 estático estático y ordenado de valores. Son equivalentes a los tipos enum soportados en un número de lenguajes de programación.

A diferencia de otros tipos, los Tipos Enumerados necesitan ser creados usando el comando CREATE TYPE. Este tipo se utiliza para almacenar un conjunto estático y ordenado de valores. Por ejemplo, las direcciones de la brújula, es decir, NORTE, SUR, ESTE y OESTE o los días de la semana como se muestra a continuación –

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

Los Enumerados, una vez creados, pueden ser utilizados como cualquier otro tipo.

Tipo Geométrico

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

Nombre	Tamaño de almacenamiento	Representación	Descripción
punto	16 bytes	Punto en un plano	(x,y)
línea	32 bytes	Línea infinita (no totalmente implementada)	((x1,y1),(x2,y2))
lseg	32 bytes	Segmento de línea infinito	((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),…)
trayectoria	16+16n bytes	Trayectoria abierta
polígono	40+16n	Polígono (similar a la trayectoria cerrada)	((x1,y1),…)
Círculo	24 bytes	Círculo	<(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 utilizar estos tipos en lugar de los tipos de texto plano para almacenar direcciones de red, porque estos tipos ofrecen comprobación de errores de entrada y operadores y funciones especializados.

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

Tipo de cadena de bits

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

Text Search Type

Este tipo soporta 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 localizar los que mejor coinciden con una consulta. Hay dos tipos de datos para esto –

S. No.	Nombre & Descripción
1	tsvector Se trata de una lista ordenada de palabras distintas que han sido normalizadas para fusionar diferentes variantes de la misma palabra, denominadas como «lexemas».¡
2	tsquery Almacena los lexemas que se van a buscar y los combina respetando los operadores booleanos & (AND), | (OR) y ! (NOT). Se pueden utilizar paréntesis para reforzar la agrupación de los operadores.

Tipo UUID

Un UUID (Universally Unique Identifiers) se escribe como una secuencia de dígitos hexadecimales en minúsculas, en varios grupos separados por guiones, concretamente 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 puede utilizarse para almacenar datos XML. Para almacenar datos XML, primero hay que 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 puede utilizarse para almacenar datos JSON (JavaScript Object Notation). Estos datos también pueden ser almacenados como texto, pero el tipo de datos json tiene la ventaja de comprobar que cada valor almacenado es un valor JSON válido. También hay disponibles funciones de soporte relacionadas, que pueden ser utilizadas directamente para manejar el tipo de datos JSON de la siguiente manera.

Ejemplo	Resultado
array_to_json(‘{{1,5},{99,100}}’::int)	,]
row_to_json(row(1,’foo’))	{«f1»:1, «f2»: «foo»}

Tipo de array

PostgreSQL da la oportunidad de definir una columna de una tabla como un array multidimensional de longitud variable. Se pueden crear arrays de cualquier tipo base incorporado o definido por el usuario, tipo enum o tipo compuesto.

Declaración de Arrays

El tipo de array puede declararse 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, scheme text);

Inserción de valores

Los valores de los arrays pueden insertarse como una constante literal, encerrando los valores de los elementos 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 Arrays

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

SELECT name FROM monhly_savings WHERE saving_per_quarter > saving_per_quarter;

Modificación de Arrays

Un ejemplo de modificación de arrays es el que se muestra a continuación.

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

o utilizando la sintaxis de la expresión ARRAY –

UPDATE monthly_savings SET saving_per_quarter = ARRAYWHERE name = 'Manisha';

Búsqueda de arrays

Un ejemplo de búsqueda de arrays es el que se muestra a continuación.

SELECT * FROM monthly_savings WHERE saving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000 ORsaving_per_quarter = 10000;

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

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 puede utilizarse en las tablas de creación como se indica a continuación –

CREATE TABLE on_hand ( item inventory_item, count integer);

Entrada de valores compuestos

Los valores compuestos pueden insertarse 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 en realidad 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 similar a la selección de un campo a partir del nombre de una 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 utilizar el nombre de la tabla también (por ejemplo, en una consulta multitable), 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. Los tipos de rango pueden ser rangos discretos (por ejemplo, todos los valores enteros de 1 a 10) o rangos continuos (por ejemplo, cualquier punto en el tiempo entre las 10:00am y las 11:00am).

Los tipos de rangos construidosen los tipos de rangos 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 rangos personalizados para disponer de nuevos tipos de rangos, como rangos de direcciones IP utilizando el tipo inet como base, o rangos de flotación utilizando el tipo de datos float 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 enteros comenzando desde e incluyendo 4 hasta pero sin incluir 9.

Tipos de Identificadores de Objetos

Los identificadores de objetos (OIDs) son usados internamente por PostgreSQL como claves primarias para varias tablas del sistema. Si se especifica WITH OIDS o se habilita la variable de configuración default_with_oids, sólo entonces, en estos casos los OIDs se añaden a las tablas creadas por el usuario. La siguiente tabla enumera varios tipos de alias. Los tipos de alias OID no tienen operaciones propias, excepto las rutinas de entrada y salida especializadas.

Nombre		Descripción	Valor Ejemplo
oid	cualquier	numérico identificador de objeto	564182
regproc	pg_proc	nombre de función	suma
regprocedimiento	pg_proc	función con tipos de argumentos	suma(int4)
regoper	pg_operador	nombre del operador	+
regoperator	pg_operator	operador con tipos de argumento	*(integer,entero) o -(NONE,integer)
regclass	pg_class	relation name	pg_type
regtype	pg_type	nombre del tipo de datos	integer
regconfig	pg_ts_config	configuración de búsqueda de texto	Español
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 colectivamentede propósito especial que se denominan colectivamente pseudotipos. Un pseudotipo no puede ser usado como un tipo de datos de columna, pero puede ser usado para declarar el argumento de una función o el tipo de resultado.

La tabla dada a continuación lista los pseudotipos existentes.

S. No.	Nombre & Descripción
1	cualquier 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 array.
4	anynonarray Indica que una función acepta cualquier tipo de datos no array.
5	anyenum Indica que una función acepta cualquier tipo de datos enum.
6	anyrange Indica que una función acepta cualquier tipo de datos range.
7	cstring Indica que una función acepta o devuelve una cadena C terminada en cero.
8	internal Indica que una función acepta o devuelve un tipo de datos interno del servidor.
9	language_handler Se declara que un manejador de llamadas de lenguaje procedimental devuelve language_handler.
10	fdw_handler Se declara un manejador de envoltura de datos extranjeros para devolver fdw_handler.
11	record Identifica una función que devuelve un tipo de fila no especificado.
12	trigger Se declara una función trigger para devolver trigger.
13	void Indica que una función no devuelve ningún valor.