Postgresql tipo de datos

Acerca del proyecto

Vamos a conocer los tipos de datos que soporta postgresql, nos vamos a basar en la documentación original y en varios libros creados que se pueden encontrar desde la pagina web de postgres. Mencionaremos una descripción breve de cada tipo de datos y en algunos daremos un ejemplo de como usarlo.

Conocimiento previo

Para realizar este proyecto se requiere conocimiento previo de los siguientes temas, si aun no has trabajado con lo que se menciona en la siguiente tabla, allí están los links a los post para que puedas dar un repaso adicional o puedas recordar el conocimiento necesario para poder realizar este proyecto.

Tipos de datos

Postgres tiene un rico conjunto de tipos de datos nativos disponibles para los usuarios. Los usuarios pueden agregar nuevos tipos a Postgres usando el comando DEFINE TYPE.

En las siguientes secciones se analizarán el cumplimiento de los estándares SQL, los problemas de portabilidad y el uso. Algunos tipos de Postgres corresponden directamente a los tipos compatibles con SQL92. En otros casos, los tipos de datos definidos por la sintaxis SQL92 se asignan directamente a los tipos nativos de Postgres. Muchos de los tipos incorporados tienen formatos externos obvios. Sin embargo, varios tipos son exclusivos de Postgres, como las rutas abiertas y cerradas, o tienen varias posibilidades de formatos, como los tipos de fecha y hora.[1]

En este post, discutiremos sobre 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:

• Consistencia: las operaciones contra columnas del mismo tipo de datos dan resultados consistentes y generalmente son 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 alcance 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 de datos más eficiente. Los valores almacenados se pueden procesar rápidamente, lo que mejora el rendimiento.

Tipos numéricos

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

Tipos entero

Los tipos smallint, integer y bigint almacenan números enteros, es decir, números sin componentes fraccionales, de varios rangos. Los intentos de almacenar valores fuera del rango permitido generarán un error.

El tipo entero es la opción común, ya que ofrece el mejor equilibrio entre rango, tamaño de almacenamiento y rendimiento. El tipo de letra pequeña generalmente solo se usa si el espacio en disco es escaso. El tipo bigint está diseñado para usarse cuando el rango del tipo entero es insuficiente.

SQL solo especifica los tipos enteros integer (o int), smallint y bigint. Los nombres de tipo int2, int4 e int8 son extensiones, que también utilizan otros sistemas de bases de datos SQL.

Tipos punto flotante

Los tipos de datos real, double y precision son tipos numéricos inexactos de precisión variable. En todas las plataformas compatibles actualmente, estos tipos son implementaciones de IEEE Standard 754 para aritmética de punto flotante binario (precisión simple y doble, respectivamente), en la medida en que el procesador, el sistema operativo y el compilador subyacentes lo admitan.

Inexacto significa que algunos valores no se pueden convertir exactamente al formato interno y se almacenan como aproximaciones, por lo que almacenar y recuperar un valor puede mostrar ligeras discrepancias. Gestionar estos los errores y la forma en que se propagan a través de los cálculos es el tema de toda una rama de las matemáticas y la informática y no se discutirá aquí, excepto por los siguientes puntos:

• Si necesita almacenamiento y cálculos exactos (como para montos monetarios), use el tipo numeric en su lugar.
• Si desea hacer cálculos complicados con estos tipos para algo importante, especialmente si confía en cierto comportamiento en casos límite (infinito, subflujo), debe evaluar la implementación cuidadosamente.
• La comparación de dos valores de punto flotante para la igualdad podría no funcionar siempre como se esperaba.

En todas las plataformas compatibles actualmente, el tipo real tiene un rango de alrededor de 1E-37 a 1E + 37 con una precisión de al menos 6 dígitos decimales. El tipo de doble precisión tiene un rango de alrededor de 1E-307 a 1E + 308 con una precisión de al menos 15 dígitos. Los valores que son demasiado grandes o demasiado pequeños causarán un error. El redondeo puede tener lugar si la precisión de un número de entrada es demasiado alta. Los números demasiado cercanos a cero que no son representables como distintos de cero causarán un error de flujo insuficiente.

Por defecto, los valores de coma flotante se emiten en forma de texto en su representación decimal precisa más corta; el valor decimal producido está más cerca del verdadero valor binario almacenado que de cualquier otro valor representable en la misma precisión binaria. (Sin embargo, el valor de salida actualmente nunca está exactamente a mitad de camino entre dos valores representables, para evitar un error generalizado donde las rutinas de entrada sí lo hacen no respeta adecuadamente la regla de redondeo a par más cercano). Este valor utilizará como máximo 17 dígitos decimales significativos para los valores float8 y como máximo 9 dígitos para los valores float4.

Tipos de serie

Los tipos de datos smallserial, serial y bigserial no son tipos verdaderos, sino simplemente una conveniencia de notación para reconectar columnas de identificador único (similar a la propiedad AUTO_INCREMENT admitida por algunas otras bases de datos). En la implementación actual, especificando:

CREATE TABLE tablename (
 colname SERIAL
);

--Es equivalente a: 

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
 colname integer NOT NULL DEFAULT
 nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Por lo tanto, hemos creado una columna entera y organizada para que sus valores predeterminados se asignen desde un generador de secuencia. Se aplica una restricción NOT NULL para garantizar que no se pueda insertar un valor nulo.

(En la mayoría de los casos, también querrá adjuntar una restricción de CLAVE ÚNICA o PRIMARIA para evitar que se inserten valores duplicados por accidente, pero esto no es utomático). Por último, la secuencia se marca como "propiedad de" la columna, de modo que se descartará si se descarta la columna o tabla.

Tipos monetarios

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

Tipos de caracteres

La siguiente tabla enumera los tipos de caracteres de uso general disponibles en PostgreSQL.

Tipos de datos binarios

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

Tipos de fecha / hora

PostgreSQL admite un conjunto completo de tipos de fecha y hora de SQL, como se muestra en la tabla a continuación. 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 el día.

Tipo booleano

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

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 utilizando 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 usar 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 otros tipos.

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 los 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.

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 variable (n) , donde n es un entero positivo.

Tipo de búsqueda de texto

Este tipo admite la búsqueda de texto completo, que es la actividad de buscar en una colección de documentos en lenguaje natural para localizar los que mejor se ajustan a una consulta. Hay dos tipos de datos para esto:

Esto almacena los lexemas que se deben buscar y los combina en honor a los operadores booleanos & (AND), | (O y ! (NO). Los paréntesis se pueden usar para imponer la agrupación de los operadores.

Tipo de UUID

Un UUID (identificadores únicos universales) 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, seguidos por 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 usar 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>Código Electrónica</title>
    <topics>...</topics>
    </tutorial>')
XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Tipo JSON

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

Tipo de matriz

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

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 usando 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 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 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];

Modificación de matrices

Un ejemplo de modificación de matrices es como 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 es el que 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 proporcionado 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, la 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 usar en las tablas de creación de la siguiente manera:

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, use un punto seguido del nombre del campo, al igual que 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 multitarea), de esta manera:

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 usan un rango de datos. El tipo de rango puede ser rangos discretos (por ejemplo, todos los valores enteros del 1 al 10) o rangos continuos (por ejemplo, cualquier punto en el tiempo entre las 10:00 a.m. y las 11:00 a.m.).

Los tipos de rango integrados disponibles incluyen los siguientes rangos:

• int4range - Rango de enteros
• 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 - Gama de la fecha

Se pueden crear tipos de rango personalizados para hacer que estén disponibles nuevos tipos de rangos, como los rangos de direcciones IP que usan el tipo de entrada como base, o los rangos flotantes que usan 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 enteros que comienzan desde 4 e incluyen 4 hasta, pero no incluyen 9.