Tipos de datos de PostgreSQL

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

Beneficios de configurar los tipos de datos:

PostgreSQL ofrece una gran variedad de tipos de datos. Los usuarios pueden usar el comando CREATE TYPE para crear nuevos tipos de datos en la base de datos. PostgreSQL tiene muchos tipos de datos; a continuación los explicamos en detalle.

Tipos numéricos

Los tipos numéricos consisten en enteros de 2, 4 u 8 bytes, números de punto flotante de 4 u 8 bytes, y números decimales de precisión opcional.

La siguiente tabla enumera los tipos numéricos disponibles.

Nombre	Longitud de almacenamiento	Descripción	Rango
smallint	2 bytes	Entero de rango pequeño	-32768 a +32767
integer	4 bytes	Entero común	-2147483648 a +2147483647
bigint	8 bytes	Entero de rango grande	-9223372036854775808 a +9223372036854775807
decimal	Longitud variable	Precisión especificada por el usuario, exacto	131072 dígitos antes del punto decimal; 16383 dígitos después del punto decimal
numeric	Longitud variable	Precisión especificada por el usuario, exacto	131072 dígitos antes del punto decimal; 16383 dígitos después del punto decimal
real	4 bytes	Precisión variable, no exacto	Precisión de 6 dígitos decimales
double precision	8 bytes	Precisión variable, no exacto	Precisión de 15 dígitos decimales
smallserial	2 bytes	Entero de rango pequeño autoincremental	1 a 32767
serial	4 bytes	Entero autoincremental	1 a 2147483647
bigserial	8 bytes	Entero de rango grande autoincremental	1 a 9223372036854775807

Tipos de moneda

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

Los valores de los tipos numeric, int y bigint se pueden convertir a money. No se recomienda usar números de punto flotante para manejar tipos de moneda, porque existe la posibilidad de errores de redondeo.

Nombre	Capacidad de almacenamiento	Descripción	Rango
money	8 bytes	Monto de moneda	-92233720368547758.08 a +92233720368547758.07

Tipos de caracteres

La siguiente tabla enumera los tipos de caracteres compatibles con PostgreSQL:

Número	Nombre y descripción
1	character varying(n), varchar(n) Longitud variable, con límite de longitud
2	character(n), char(n) Longitud fija, se rellena con espacios si es insuficiente
3	text Longitud variable, sin límite de longitud

Número

Nombre y descripción

character varying(n), varchar(n)

Longitud variable, con límite de longitud

character(n), char(n)

Longitud fija, se rellena con espacios si es insuficiente

text

Longitud variable, sin límite de longitud

Tipos de fecha/hora

La siguiente tabla enumera los tipos de fecha y hora compatibles con PostgreSQL.

Nombre	Espacio de almacenamiento	Descripción	Valor mínimo	Valor máximo	Resolución
timestamp [ (p) ] [ without time zone ]	8 bytes	Fecha y hora (sin zona horaria)	4713 BC	294276 AD	1 milisegundo / 14 dígitos
timestamp [ (p) ] with time zone	8 bytes	Fecha y hora, con zona horaria	4713 BC	294276 AD	1 milisegundo / 14 dígitos
date	4 bytes	Solo para fecha	4713 BC	5874897 AD	1 día
time [ (p) ] [ without time zone ]	8 bytes	Solo para hora del día	00:00:00	24:00:00	1 milisegundo / 14 dígitos
time [ (p) ] with time zone	12 bytes	Solo para hora del día, con zona horaria	00:00:00+1459	24:00:00-1459	1 milisegundo / 14 dígitos
interval [ fields ] [ (p) ]	12 bytes	Intervalo de tiempo	-178000000 años	178000000 años	1 milisegundo / 14 dígitos

Tipo booleano

PostgreSQL admite el tipo de datos boolean estándar.

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

Nombre	Formato de almacenamiento	Descripción
boolean	1 byte	true/false

Tipo enumerado

Un tipo enumerado es un tipo de datos que contiene un conjunto ordenado de valores estáticos.

El tipo enumerado en PostgreSQL es similar al tipo enum en C.

A diferencia de otros tipos, el tipo enumerado debe crearse con el comando CREATE TYPE.

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

Crear los días de la semana, como se muestra a continuación:

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

Al igual que otros tipos, una vez creado, el tipo enumerado se puede usar en definiciones de tablas y funciones.

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

Tipos geométricos

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

La siguiente tabla enumera los tipos geométricos compatibles con PostgreSQL.

El tipo más básico: el punto. Es la base de los demás tipos.

Nombre	Espacio de almacenamiento	Descripción	Representación
point	16 bytes	Punto en el plano	(x,y)
line	32 bytes	Línea recta (infinita) (no completamente implementada)	((x1,y1),(x2,y2))
lseg	32 bytes	Segmento de línea (finito)	((x1,y1),(x2,y2))
box	32 bytes	Rectángulo	((x1,y1),(x2,y2))
path	16+16n bytes	Camino cerrado (similar a polígono)	((x1,y1),...)
path	16+16n bytes	Camino abierto	[(x1,y1),...]
polygon	40+16n bytes	Polígono (similar a camino cerrado)	((x1,y1),...)
circle	24 bytes	Círculo	<(x,y),r> (centro y radio)

Tipos de dirección de red

PostgreSQL proporciona tipos de datos para almacenar direcciones IPv4, IPv6 y MAC.

Usar estos tipos de datos para almacenar direcciones de red es mejor que usar tipos de texto plano, porque estos tipos ofrecen verificación de errores de entrada y operaciones y funciones especiales.

Nombre	Espacio de almacenamiento	Descripción
cidr	7 o 19 bytes	Red IPv4 o IPv6
inet	7 o 19 bytes	Host y red IPv4 o IPv6
macaddr	6 bytes	Dirección MAC

Al ordenar los tipos de datos inet o cidr, las direcciones IPv4 siempre aparecen antes que las direcciones IPv6, incluidas aquellas direcciones IPv4 encapsuladas o asignadas dentro de direcciones IPv6, como ::10.2.3.4 o ::ffff:10.4.3.2.

Tipos de cadena de bits

Una cadena de bits es una cadena de 1 y 0. Se pueden usar para almacenar y visualizar máscaras de bits. Tenemos dos tipos de bits SQL: bit(n) y bit varying(n), donde n es un entero positivo.

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

Tipos de búsqueda de texto

La búsqueda de texto completo consiste en recuperar, de una colección de documentos en lenguaje natural, aquellos que coinciden con una consulta.

PostgreSQL proporciona dos tipos de datos para admitir la búsqueda de texto completo:

N.º	Nombre y descripción
1	tsvector El valor de tsvector es una lista ordenada de lexemas sin duplicados, es decir, variantes normalizadas de una misma palabra.
2	tsquery tsquery almacena lexemas para la búsqueda y los combina mediante los operadores booleanos & (Y), \| (O) y ! (NO); los paréntesis se utilizan para forzar la agrupación de operadores.

N.º

Nombre y descripción

tsvector

El valor de tsvector es una lista ordenada de lexemas sin duplicados, es decir, variantes normalizadas de una misma palabra.

tsquery

tsquery almacena lexemas para la búsqueda y los combina mediante los operadores booleanos & (Y), | (O) y ! (NO); los paréntesis se utilizan para forzar la agrupación de operadores.

Tipo UUID

El tipo de datos uuid se utiliza para almacenar identificadores únicos universales (UUID) definidos en RFC 4122, ISO/IEF 9834-8:2005 y normas relacionadas. (Algunos sistemas denominan a este tipo de datos identificador único global, o GUID.) Este identificador es un identificador de 128 bits generado por un algoritmo, lo que hace imposible que sea igual a identificadores generados por otros métodos en módulos que se sabe que utilizan el mismo algoritmo. Por lo tanto, para los sistemas distribuidos, este identificador ofrece una mejor garantía de unicidad que las secuencias, ya que las secuencias solo pueden garantizar la unicidad dentro de una única base de datos.

Un UUID se escribe como una secuencia de dígitos hexadecimales en minúsculas, agrupados por guiones: específicamente, un grupo de 8 dígitos + 3 grupos de 4 dígitos + un grupo de 12 dígitos, en total 32 dígitos que representan 128 bits. Un ejemplo de un UUID estándar de este tipo es el siguiente:

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

Tipo XML

El tipo de datos xml se puede utilizar para almacenar datos XML. La ventaja de almacenar datos XML en el tipo text es que permite comprobar que los valores de entrada estén bien formados y además admite funciones para realizar comprobaciones de seguridad de tipos sobre ellos. Para utilizar este tipo de datos, debe usarse al compilar.configure --with-libxml。

xml puede almacenar «documentos» bien formados definidos por el estándar XML, así como los del estándar XML.XMLDecl? contentLos «fragmentos de contenido» definidos; en términos generales, esto significa que los fragmentos de contenido pueden tener varios elementos de nivel superior o nodos de carácter. La expresión xmlvalue IS DOCUMENT puede utilizarse para determinar si un valor xml concreto es un documento completo o un fragmento de contenido.

Creación de valores XML

Utilice la función xmlparse: para generar valores de tipo xml a partir de datos de caracteres:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

Tipo JSON

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

Además, existen funciones relacionadas para procesar datos json:

Ejemplo	Resultado del ejemplo
array_to_json('{{1,5},{99,100}}'::int[])	[[1,5],[99,100]]
row_to_json(row(1,'foo'))	{"f1":1,"f2":"foo"}

Tipos de matriz

PostgreSQL permite definir campos como arrays multidimensionales de longitud variable.

Los tipos de array pueden ser cualquier tipo base o definido por el usuario, un tipo enumerado o un tipo compuesto.

Declaración de matrices

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

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter es un array unidimensional de enteros y schedule es un array bidimensional de tipo text.

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

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

Insertar valores

Para insertar valores se utilizan llaves {}; los elementos dentro de {} se separan con comas:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Acceso a matrices

Ahora podemos ejecutar algunas consultas sobre esta tabla.

Primero, mostramos cómo acceder a un elemento de un array. Esta consulta recupera los nombres de los empleados cuyo salario cambió en el segundo trimestre:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

Los números de subíndice de los arrays se escriben entre corchetes.

Modificación de matrices

Podemos modificar los valores de un array:

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

O utilizar la sintaxis del constructor ARRAY:

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

Búsqueda en matrices

Para buscar un valor en un array, debe examinar cada valor del array.

Por ejemplo:

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;

Alternativamente, puede utilizar la siguiente sentencia para encontrar las filas en las que todos los elementos del array son iguales a 10000:

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

O bien, puede utilizar la función generate_subscripts. Por ejemplo:

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;

Tipos compuestos

Un tipo compuesto representa la estructura de una fila o de un registro; en realidad es solo una lista de nombres de campos y sus tipos de datos. PostgreSQL permite utilizar los tipos compuestos como tipos de datos simples. Por ejemplo, un campo de una tabla puede declararse como un tipo compuesto.

Declaración de tipos compuestos

A continuación se muestran dos ejemplos sencillos de definición de tipos compuestos:

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, excepto que aquí solo se pueden declarar nombres de campos y tipos.

Una vez definido el tipo, podemos utilizarlo para crear una tabla:

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

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

Entrada de valores de tipos compuestos

Para escribir un valor de tipo compuesto como constante de texto, encierre los valores de los campos entre paréntesis y sepárelos con comas. Puede poner comillas dobles alrededor de cualquier valor de campo; si el valor en sí contiene comas o paréntesis, debe encerrarlo entre comillas dobles.

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

'( val1 , val2 , ... )'

Un ejemplo es:

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

Acceso a tipos compuestos

Para acceder a un campo de un tipo compuesto, escribimos un punto y el nombre del campo, de manera muy similar a seleccionar un campo a partir de un nombre de tabla. De hecho, como es muy similar a seleccionar un campo de un nombre de tabla, a menudo necesitamos usar paréntesis para evitar que el analizador se confunda. Por ejemplo, podría necesitar seleccionar algunos subcampos de la tabla de ejemplo 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 SQL, item se selecciona de un nombre de tabla, no de un nombre de campo. Debe escribirlo de la siguiente manera:

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

O si también necesita utilizar el nombre de tabla (por ejemplo, en una consulta multitabla), entonces escríbalo así:

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

Ahora el objeto entre paréntesis se analiza correctamente como una referencia al campo item, y de él puede seleccionar subcampos.

Tipos de rango

Los tipos de datos de rango representan un rango de valores de un determinado tipo de elemento.

Por ejemplo, un rango de timestamp podría utilizarse para representar el intervalo de tiempo en el que una sala de reuniones está reservada.

Los tipos de rango integrados en PostgreSQL son:

int4range — rango de integer
int8range — rango de bigint
numrange — rango de numeric
tsrange — rango de timestamp without time zone
tstzrange — rango de timestamp with time zone
daterange — rango de date

Además, puede definir sus propios tipos de rango.

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上边界
SELECT upper(int8range(15, 25));

-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 范围是否为空
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]
vacío

Los paréntesis o corchetes indican si el límite inferior y el superior son excluyentes o incluyentes. Observe que el último formato es empty, lo que representa un rango vacío (un rango que no contiene valores).

-- 包括3，不包括7，并且包括二者之间的所有点
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之间所有点
SELECT '(3,7)'::int4range;

-- 只包括单一值4
SELECT '[4,4]'::int4range;

-- 不包括点（被标准化为‘空’）
SELECT '[4,4)'::int4range;

Tipos de identificador de objeto

PostgreSQL utiliza internamente identificadores de objeto (OID) como claves primarias de varias tablas del sistema.

Asimismo, el sistema no añade un campo de sistema OID a las tablas creadas por el usuario (a menos que se declare WITH OIDS al crear la tabla o que el parámetro de configuración default_with_oids esté activado). El tipo oid representa un identificador de objeto. Además de esto, oid tiene varios alias: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig y regdictionary.

Nombre	Referencia	Descripción	Ejemplo numérico
oid	Cualquiera	Identificador de objeto numérico	564182
regproc	pg_proc	Nombre de función	sum
regprocedure	pg_proc	Función con tipos de argumento	sum(int4)
regoper	pg_operator	Nombre de operador	+
regoperator	pg_operator	Operador con tipos de argumento	*(integer,integer) o -(NONE,integer)
regclass	pg_class	Nombre de relación	pg_type
regtype	pg_type	Nombre de tipo de datos	integer
regconfig	pg_ts_config	Configuración de búsqueda de texto	english
regdictionary	pg_ts_dict	Diccionario de búsqueda de texto	simple

Pseudotipos

El sistema de tipos de PostgreSQL contiene una serie de elementos con fines especiales que, por categoría, se denominan pseudotipos. Un pseudotipo no puede utilizarse como tipo de datos de un campo, pero puede utilizarse para declarar el tipo de un argumento o el resultado de una función. Los pseudotipos resultan útiles cuando una función no se limita a aceptar y devolver un tipo de datos SQL determinado.

La siguiente tabla enumera todos los pseudotipos:

Nombre	Descripción
any	Indica que una función acepta cualquier tipo de datos de entrada.
anyelement	Indica que una función acepta cualquier tipo de datos.
anyarray	Indica que una función acepta cualquier tipo de datos de array.
anynonarray	Indica que una función acepta cualquier tipo de datos que no sea de array.
anyenum	Indica que una función acepta cualquier tipo de datos enumerado.
anyrange	Indica que una función acepta cualquier tipo de datos de rango.
cstring	Indica que una función acepta o devuelve una cadena C terminada en nulo.
internal	Indica que una función acepta o devuelve un tipo de datos interno del servidor.
language_handler	Un manejador de llamadas de lenguaje procedural se declara que devuelve language_handler.
fdw_handler	Un contenedor de datos externo se declara que devuelve fdw_handler.
record	Identifica que una función devuelve un tipo de fila no declarado.
trigger	Una función de disparador (trigger) se declara que devuelve trigger.
void	Indica que una función no devuelve ningún valor.
opaque	Un tipo obsoleto, que antes se utilizaba para todos los propósitos mencionados anteriormente.

Más referencias:Tipos de datos de PostgreSQL

Otras extensiones

PostgreSQL Avanzado