• Author / Uploaded
• Cronicas de una Lectora Venezolana

Citation preview

Republica Bolivariana de Venezuela Ministerio del Poder Popular para la Educacion Universidad Alonso de Ojeda Facultad de ingenieria Escuela de Computacion

POSTGRESQL

Estudiantes: Hercymar Medina Linda Der Sarkisian Rasha Hadam Argenisys Duno Diana Medina

1. ¿QUE ES POSTGRESQL? PostgreSQL es un sistema de gestión de bases de datos de objeto general y relacional, el sistema de base de datos de código abierto más avanzado. PostgreSQL fue diseñado para ejecutarse en plataformas similares a UNIX. Sin embargo, PostgreSQL también fue diseñado para ser portátil de modo que pudiera ejecutarse en varias plataformas como Mac OS X, Solaris y Windows. PostgreSQL es un software gratuito y de código abierto. Su código fuente está disponible bajo licencia PostgreSQL, una licencia liberal de código abierto. Usted es libre de usar, modificar y distribuir PostgreSQL en cualquier forma.PostgreSQL requiere esfuerzos mínimos mantenidos debido a su estabilidad. Características destacadas de PostgreSQL PostgreSQL tiene muchas características avanzadas que ofrecen otros sistemas de gestión de bases de datos empresariales, como: • • • • • • • •

Tipos definidos por el usuario Herencia de la tabla Sofisticado mecanismo de bloqueo Integridad referencial de clave externa Vistas , reglas, subconsulta Transacciones anidadas (puntos de guardado) Control de concurrencia de versiones múltiples (MVCC) Replicación asincrónica

2. ¿COMO CONECTAR LA BASE DE DATOS? Puede conectarse al servidor de base de datos PostgreSQL a través del programa interactivo psql o la herramienta pgAdmin. Psql: es un programa de terminal interactivo proporcionado por PostgreSQL. Le permite interactuar con el servidor de bases de datos PostgreSQL, como ejecutar sentencias SQL y administrar objetos de bases de datos. Conectando la base de datos PostgreSQL a través del programa psql : • •

Primero, inicie el programa psql y conéctese al servidor de base de datos PostgreSQL utilizando el usuario postgres haciendo clic en el icono psql. En segundo lugar, ingrese la información necesaria, como Servidor, Base de datos, Puerto, Nombre de usuario y Contraseña. Presione Entrar para aceptar el valor predeterminado. Sin embargo, debe ingresar la contraseña que configuró durante la instalación .

• •

Tercero, interactúe con el servidor de base de datos PostgreSQL emitiendo una declaración SQL. No olvide finalizar el comando con punto y coma (;). Después de presionar Enter , psql devolverá la versión actual de PostgreSQL que tiene en el sistema.

La segunda forma de conectarse a una base de datos es mediante la aplicación pgAdmin. Al usar la aplicación pgAdmin, puede interactuar con el servidor de base de datos PostgreSQL a través de una interfaz de usuario intuitiva. A continuación se ilustra cómo conectarse a una base de datos utilizando la aplicación pgAdmin: • • • • • • •

Primero, inicie la aplicación pgAdmin. La aplicación pgAdmin versión 4 se iniciará en el navegador web como se muestra en la siguiente imagen: En segundo lugar, haga clic con el botón derecho en el nodo Servidores y seleccione Crear> Servidor ... para crear un servidor Tercero, ingrese el nombre del servidor, por ejemplo, PostgreSQL y haga clic en la pestaña Conexión Cuarto, ingrese el host y la contraseña para el usuario de postgres y haga clic en el botón Guardar : Quinto, haga clic en el nodo Servidores para expandir el servidor. Por defecto, PostgreSQL tiene una base de datos llamada postgres como se muestra a continuación: Sexto, abra la herramienta de consulta seleccionando el elemento de menú Herramienta> Herramienta de consulta o haga clic en el icono del rayo. Séptimo, ingrese la consulta en el Editor de consultas , haga clic en el botón Ejecutar , verá el resultado de la consulta en la pestaña Salida de datos

3. SERVIDOR POSTGRESQL Y OBJETOS DE BASE DE DATOS. Servicio de servidor: Cuando instale una instancia de PostgreSQL, tendrá un servicio de servidor PostgreSQL correspondiente. También se conoce como servidor PostgreSQL. Puede instalar varios servidores PostgreSQL en un servidor físico utilizando diferentes puertos y teniendo diferentes ubicaciones para almacenar datos. Bases de datos: Una base de datos es un contenedor de otros objetos como tablas, vistas , funciones e índices . Puede crear tantas bases de datos como desee dentro de un servidor PostgreSQL. Tablas: La tabla se usa para almacenar los datos. Puede tener muchas tablas en una base de datos. Una característica especial de PostgreSQL es la herencia de tablas. Significa que una tabla (tabla secundaria) puede heredar de otra tabla

(tabla primaria), de modo que cuando consulta datos de la tabla secundaria, también se muestran los datos de la tabla primaria. Esquemas: Un esquema es un contenedor lógico de tablas y otros objetos dentro de una base de datos. Cada base de datos PostgreSQL puede tener múltiples esquemas. Es importante tener en cuenta que el esquema es parte del estándar ANSI-SQL. Espacio de tabla: Un espacio de tabla es donde PostgreSQL almacena los datos. El espacio de tabla de PostgreSQL le permite mover sus datos a diferentes ubicaciones físicas a través de controladores fácilmente mediante el uso de comandos simples. De manera predeterminada, PostgreSQL proporciona dos espacios de tabla: pg_default para almacenar datos del usuario y pg_global para almacenar datos del sistema. Puntos de vista: Una vista es una tabla virtual que se utiliza para simplificar consultas complejas y para aplicar seguridad a un conjunto de registros. PostgreSQL también le proporciona vistas actualizables . Las funciones: Una función es un código SQL reutilizable en bloque que devuelve un valor escalar de una lista de registros. En PostgreSQL, las funciones también pueden devolver objetos compuestos. Operadores: Los operadores son funciones simbólicas. PostgreSQL le permite definir operadores personalizados. Moldes :Las conversiones le permiten convertir un tipo de datos en otro tipo de datos. Casts realmente respaldados por funciones para realizar la conversión. También puede crear sus propios lanzamientos para anular el lanzamiento predeterminado proporcionado por PostgreSQL. Secuencia: Las secuencias se usan para administrar columnas de incremento automático que se definen en una tabla como una columna en serie . Extensión: PostgreSQL introdujo el concepto de extensión desde la versión 9.1 para envolver otros objetos, incluidos tipos, conversiones, índices, funciones, etc., en una sola unidad. El propósito de las extensiones es facilitar su mantenimiento. 4. FUNDAMENTOS DE POSTGRESQL Funcion Select: Una de las tareas más comunes, cuando trabaja con PostgreSQL, es consultar los datos de las tablas utilizando la instrucción

SELECT. La declaración SELECT es una de las declaraciones más complejas en PostgreSQL. Tiene muchas cláusulas que utiliza para formar una consulta flexible. La declaración SELECT tiene las siguientes cláusulas: • • • • • • • •

Seleccione filas distintas usando el DISTINCT operador. Ordenar filas usando la ORDER BY cláusula Filtrar filas usando la WHERE cláusula Seleccione un subconjunto de filas de una tabla usando LIMIT o FETCH cláusula. Agrupe las filas en grupos usando la GROUP BY cláusula Filtrar grupos utilizando la HAVING cláusula Unirse a otras tablas utilizando une como INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN cláusulas. Realizar operaciones de conjuntos utilizando UNION, INTERSECT y EXCEPT.

Funcion order by: Cuando consulta datos de una tabla, PostgreSQL devuelve las filas en el orden en que se insertaron en la tabla. Para ordenar el conjunto de resultados, utilice la cláusula ORDER BY en la declaración SELECT. La cláusula ORDER BY le permite ordenar las filas devueltas de la declaración SELECT en orden ascendente o descendente según los criterios especificados. Funcion select distint: La cláusula DISTINCT se usa en la instrucción SELECT para eliminar filas duplicadas de un conjunto de resultados. La cláusula DISTINCT mantiene una fila para cada grupo de duplicados. La cláusula DISTINCT se puede usar en una o más columnas de una tabla. Funcion where: La cláusula WHERE aparece justo después de la cláusula FROM de la declaración SELECT. Los usos de la cláusula WHERE de los condition que va a filtrar las filas devueltas desde la declaración SELECT. Los condition debe evaluar como verdadero, falso o desconocido. Puede ser una expresión booleana o una combinación de expresiones booleanas utilizando AND y OR operadores. La consulta devuelve las filas que satisfacen el conditionen la cláusula WHERE. En otras palabras, solo las filas que causan que la condición se evalúe como verdadera se incluirán en el conjunto de resultados. Además de la declaración SELECT, puede usar la cláusula WHERE en la declaración UPDATE y DELETE para especificar las filas que se actualizarán o eliminarán. Funcion Limit: PostgreSQL LIMIT es una cláusula opcional de la declaración SELECT que devuelve un subconjunto de filas devueltas por la consulta.

Funcion fetch: Para limitar el número de filas devueltas por una consulta, a menudo utiliza la cláusula LIMIT. La cláusula LIMIT es ampliamente utilizada por muchos sistemas de gestión de bases de datos relacionales como MySQL, H2 y HSQLDB. Sin embargo, la cláusula LIMIT no es un estándar SQL. Para cumplir con el estándar SQL, PostgreSQL proporciona la cláusula FETCH para recuperar una parte de las filas devueltas por una consulta. Debido a que el orden de las filas almacenadas en la tabla es impredecible, debe usar la cláusula FETCH con la cláusula ORDER BY para que el conjunto de resultados sea coherente. Funcion in: Utiliza el operador IN en la cláusula WHERE para verificar si un valor coincide con algún valor en una lista de valores. Funcion not in: Puede combinar el operador IN con el operador NOT para seleccionar filas cuyos valores no coincidan con los valores de la lista. Funcion between: Utiliza el operador BETWEEN para hacer coincidir un valor con un rango de valores. Funcion like: El operador LIKE se utiliza para que coincida con los valores de texto frente a un patrón de uso de comodines. Si la expresión de búsqueda puede coincidir con la expresión del patrón, el operador LIKE devolverá verdadero, que es 1 . Hay dos comodines utilizados junto con el operador LIKE:  

El signo de porcentaje (%) El guión bajo (_)

El signo de porcentaje representa cero, uno o múltiples números o caracteres. El guión bajo representa un solo número o carácter. Estos símbolos se pueden usar en combinaciones. Si alguno de estos dos signos no se usa junto con la cláusula LIKE, entonces LIKE actúa como el operador igual. Funcion null: NULL es el término utilizado para representar un valor faltante. Un valor NULL en una tabla es un valor en un campo que parece estar en blanco.Un campo con un valor NULL es un campo sin valor. Es muy importante comprender que un valor NULL es diferente de un valor cero o un campo que contiene espacios. Funcion alias: Un alias de PostgreSQL asigna a una tabla o columna un nombre temporal en una consulta . Los alias solo existen durante la ejecución de la consulta.

Funcion joins: La cláusula de PostgreSQL Joins se usa para combinar registros de dos o más tablas en una base de datos. Unir es un medio para combinar campos de dos tablas mediante el uso de valores comunes a cada uno. Funcion inner join: Un INNER JOIN crea una nueva tabla de resultados al combinar los valores de columna de dos tablas (table1 y table2) basadas en el predicado de unión. La consulta compara cada fila de table1 con cada fila de table2 para encontrar todos los pares de filas, que satisfacen el predicado de unión. Cuando se cumple el predicado de unión, los valores de columna para cada par de filas coincidentes de tabla1 y tabla2 se combinan en una fila de resultados.Una UNIÓN INTERNA es el tipo de unión más común y es el tipo de unión predeterminado. Puede usar la palabra clave INNER opcionalmente. Funcion left join: En caso de INNER OUTER JOIN, primero se realiza una unión interna. Luego, para cada fila de la tabla T1 que no cumple la condición de unión con cualquier fila de la tabla T2, se agrega una fila unida con valores nulos en las columnas de T2. Por lo tanto, la tabla unida siempre tiene al menos una fila para cada fila en T1. Funcion self-join: Una autounión es una consulta en la que una tabla se une a sí misma. Las autouniones son útiles para comparar valores en una columna de filas dentro de la misma tabla. Para formar una autounión, especifique la misma tabla dos veces con diferentes alias, configure la comparación y elimine los casos en los que un valor sería igual a sí mismo. Funcion cross join: Un CROSS JOIN hace coincidir cada fila de la primera tabla con cada fila de la segunda tabla. Si las tablas de entrada tienen columnas x e y, respectivamente, la tabla resultante tendrá columnas x + y. Debido a que CROSS JOIN tiene el potencial de generar tablas extremadamente grandes, se debe tener cuidado de usarlas solo cuando sea apropiado. Funcion group by: La cláusula GROUP BY de PostgreSQL se usa en colaboración con la instrucción SELECT para agrupar esas filas en una tabla que tiene datos idénticos. Esto se hace para eliminar la redundancia en los agregados de salida y / o cálculo que se aplican a estos grupos. La cláusula GROUP BY sigue la cláusula WHERE en una instrucción SELECT y precede a la cláusula ORDER BY. Funcion having: La cláusula HAVING nos permite seleccionar filas particulares donde el resultado de la función cumple alguna condición. La cláusula WHERE coloca condiciones en las columnas seleccionadas, mientras que la cláusula HAVING coloca condiciones en los grupos creados por la cláusula GROUP BY.

Funcion interset: Al igual que los operadores UNION y EXCEPT , el operador INTERSECT PostgreSQL combina los conjuntos de resultados de dos o más instrucciones SELECT en un solo conjunto de resultados. El operador INTERSECT devuelve las filas que están disponibles en ambos conjuntos de resultados o devueltas por ambas consultas. Funcion except: Al igual que los operadores UNION e INTERSECT, el operador EXCEPT devuelve filas comparando los conjuntos de resultados de dos o más necesidades. El operador EXCEPT devuelve filas distintas de la primera consulta (izquierda) que no están en la salida de la segunda consulta (derecha). Funcion grouping sets: Un GROUPING SET es un conjunto de columnas por el que agrupa. Por lo general, una única consulta agregada define un único conjunto de agrupación. La función GROUPING acepta el nombre de una columna y devuelve el bit 0 si la columna es miembro del conjunto de agrupación actual y 1 en caso contrario. Funcion cube: PostgreSQL CUBE es una subcláusula de la cláusula GROUP BY. El CUBE permite generar múltiples conjuntos de agrupación. Un conjunto de agrupación es un conjunto de columnas a las que desea agrupar. Funcion rollup: PostgreSQL ROLLUP es una subcláusula de la cláusula GROUP BY que ofrece una abreviatura para definir múltiples conjuntos de agrupación. A diferencia de la subcláusula CUBE, ROLLUP no genera todos los conjuntos de agrupación posibles basados en las columnas especificadas. Simplemente hace un subconjunto de esos. El ROLLUP asume una jerarquía entre las columnas de entrada y genera todos los conjuntos de agrupación que tiene sentido teniendo en cuenta la jerarquía. Esta es la razón por la ROLLUP que a menudo se usa para generar los subtotales y el total general de los informes. Funcion any: El operador ANY compara un valor con un conjunto de valores devueltos por una subconsulta. Tenga en cuenta que SOME es un sinónimo de ANY, lo que significa que se puede sustituir SOME por ANY cualquier sentencia SQL. Funcion all: El operador PostgreSQL ALL le permite consultar datos comparando un valor con una lista de valores devueltos por una subconsulta . Funcion exists: El operador EXISTS se utiliza para probar la existencia de filas en una subconsulta .

Funcion insert: Cuando crea una nueva tabla , no tiene ningún dato. Lo primero que suele hacer es insertar nuevas filas en la tabla. PostgreSQL proporciona la declaración INSERT que le permite insertar una o más filas en una tabla a la vez. Funcion update: Para cambiar los valores de las columnas en una tabla, use la instrucción UPDATE. Funcion delete: La consulta DELETE de PostgreSQL se utiliza para eliminar los registros existentes de una tabla. Puede usar la cláusula WHERE con la consulta DELETE para eliminar las filas seleccionadas. De lo contrario, todos los registros serían eliminados. 5. ADMINISTRAR BASES DE DATOS CREATE DATABASE: Para crear una nueva base de datos PostgreSQL, use la declaración CREATE DATABASE. Este comando creará una base de datos desde el indicador de shell PostgreSQL, pero debe tener el privilegio apropiado para crear una base de datos. Por defecto, la nueva base de datos se creará clonando la plantilla de base de datos del sistema estándar1 . La sintaxis básica de la instrucción CREATE DATABASE es la siguiente: CREATE DATABASE dbname; donde dbname es el nombre de una base de datos para crear. La declaración CREATE DATABASE le brinda varias opciones al crear una nueva base de datos. Examinemos esas opciones con más detalle: •

• •

nombre_bd : es el nombre de la nueva base de datos que desea crear. El nombre de la base de datos debe ser único en el servidor de bases de datos PostgreSQL. Si intenta crear una nueva base de datos que tenga el mismo nombre que una base de datos existente, PostgreSQL emitirá un error. role_name : es el nombre del usuario que será el propietario de la nueva base de datos. PostgreSQL usa el nombre de rol del usuario que ejecuta la CREATE DATABASEdeclaración como el nombre de rol predeterminado. plantilla : es el nombre de la plantilla de base de datos a partir de la cual se crea la nueva base de datos. PostgreSQL le permite crear una base de datos basada en una plantilla de base de datos. La plantilla1 es la base de datos de plantilla predeterminada.

• •

•

• •

codificación : especifica la codificación del juego de caracteres para la nueva base de datos. Por defecto, es la codificación de la base de datos de la plantilla. intercalar: especifica una intercalación para la nueva base de datos. La clasificación especifica el orden de clasificación de las cadenas que afectan el resultado de la cláusula ORDER BY en la instrucción SELECT . La clasificación de la base de datos de la plantilla es la clasificación predeterminada para la nueva base de datos si no la especifica explícitamente en el LC_COLLATEparámetro. ctype : especifica la clasificación de caracteres para la nueva base de datos. El ctypeafecta la categorización, por ejemplo, dígito, inferior y superior. El valor predeterminado es la clasificación de caracteres de la base de datos de plantilla. tablespace_name : especifica el nombre del tablespace para la nueva base de datos. El valor predeterminado es el espacio de tabla de la base de datos de la plantilla. max_concurrent_connection: especifica las conexiones simultáneas máximas a la nueva base de datos. El valor predeterminado es -1, es decir, ilimitado. Esta característica es muy útil en los entornos de alojamiento compartido donde puede configurar las conexiones simultáneas máximas para una base de datos particular.

ALTER DATABASE: Una vez que haya creado una base de datos , puede cambiar sus características utilizando la instrucción ALTER DATABASE. Usted especifica el nombre de la base de datos que desea cambiar después de que ALTER DATABASE. PostgreSQL le permita realizar varias acciones en la base de datos existente. Para cambiar el nombre de la base de datos, utilice la ALTER DATABASE RENAME TO siguiente declaración: ALTER DATABASE target_database RENAME TO new_database; Para cambiar el propietario de la base de datos, utilice ALTER DATABASE OWNER TO la siguiente declaración: ALTER DATABASE target_database OWNER TO new_onwer; Para cambiar el espacio de tabla predeterminado de la base de datos, utilice lo siguiente: ALTER DATABASE target_database SET TABLESPACE new_tablespace;

La instrucción mueve tablas e índices del espacio de tabla heredado al nuevo. DROP DATABASE: Una vez que ya no se necesita una base de datos, puede eliminarla utilizando la instrucción DROP DATABASE. Lo siguiente ilustra la sintaxis de la declaración DROP DATABASE: DROP DATABASE [IF EXISTS] name; Para eliminar una base de datos: • •

Especifique el nombre de la base de datos que desea eliminar después de la cláusula DROP DATABASE. Utilícelo IF EXISTS para evitar que un error elimine una base de datos inexistente. PostgreSQL emitirá un aviso en su lugar.

La declaración DROP DATABASE elimina las entradas de catálogo y el directorio de datos de forma permanente. Esta acción no se puede deshacer, por lo que debe usarla con precaución. Solo el propietario de la base de datos puede ejecutar la declaración DROP DATABASE. Además, no puede ejecutar la declaración DROP DATABASE si hay alguna conexión activa a la base de datos. Debe conectarse a otra base de datos. 6. TIPOS DE DATOS PostgreSQL admite los siguientes tipos de datos: A. 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. Nombre

Tamaño de almacenamiento

Descripción

booleano

1 byte

estado de verdadero o falso

B. CHAR, VARCHAR y TEXT: PostgreSQL proporciona tres tipos de caracteres principales: character(n)o char(n), character varying(n)o varchar(n), y text, donde n es un entero positivo. Tipos de personajes

Descripción

character varying(n), varchar(n)

longitud variable con límite

character(n), char(n)

longitud fija, relleno en blanco

text, varchar

longitud ilimitada variable

C. NUMERICOS: 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. Nombre

Tamaño de almacenamiento

Descripción

Distancia

letra pequeña

2 bytes

entero de rango pequeño

-32768 a +32767

entero

4 bytes

elección típica para entero

-2147483648 a +2147483647

bigint

8 bytes

entero de gran alcance

-9223372036854775808 a 9223372036854775807

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,

Precisión de 6 dígitos

decimal

inexacta

decimales

Precisión doble

8 bytes

precisión variable, inexacta

Precisión de 15 dígitos decimales

pequeño serial

2 bytes

pequeño entero autoincrementador

1 a 32767

de serie

4 bytes

entero autoincrementador

1 a 2147483647

bigserial

8 bytes

gran entero autoincrementador

1 a 9223372036854775807

D. ENTERO: Esta es INTEGER la opción más común entre los tipos enteros porque ofrece el mejor equilibrio entre el tamaño de almacenamiento, el rango y el rendimiento. El tipo INTEGER requiere un tamaño de almacenamiento de 4 bytes que puede almacenar números en el rango de (-2,147,483,648, 2,147,483,647). E. 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 . Nombre

marca de tiempo [(p)] [sin zona horaria]

TIMESTAMPTZ

Tamaño de almacenamiento

Descripción

Bajo valor

Alto valor

8 bytes

tanto la fecha como la hora (sin zona horaria)

4713 a.

294276 AD

8 bytes

tanto la fecha como la hora, con zona horaria

4713 a.

294276 AD

fecha

4 bytes

fecha (sin hora del día)

4713 a.

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

F. 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. Nombre

Tamaño de almacenamiento

Representación

Descripción

punto

16 bytes

Apuntar en un avión

(x, y)

línea

32 bytes

Línea infinita (no totalmente implementada)

((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

Trayectoria cerrada (similar al polígono)

((x1, y1), ...)

camino

16 + 16n bytes

Camino abierto

[(x1, y1), ...]

polígono

40 + 16n

Polígono (similar a la ruta cerrada)

((x1, y1), ...)

circulo

24 bytes

Circulo

(punto central y radio)

G. 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. Nombre

Tamaño de almacenamiento

dinero

8 bytes

Descripción

Distancia

cantidad de moneda

-92233720368547758.08 a +92233720368547758.07

H. 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. I. 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. J. UUID: Un UUID (identificadores únicos universales) se escribe como una secuencia de dígitos hexadecimales en minúscula, 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 7. GESTIONAR TABLAS CREATE TABLE

Para crear una nueva tabla en PostgreSQL, use la instrucción CREATE TABLE. Lo siguiente ilustra la sintaxis de la declaración CREATE TABLE: CREATE TABLE table_name ( column_name TYPE column_constraint, table_constraint table_constraint ) INHERITS existing_table_name;

examinemos la sintaxis de la CREATE TABLEdeclaración con más detalle. • •

• •

Primero, especifica el nombre de la nueva tabla después de la cláusula CREATE TABLE. A continuación, enumera el nombre de la columna, su tipo de datos y la restricción de la columna. Una tabla puede tener múltiples columnas separadas por una coma (,). La restricción de columna define reglas para la columna, por ejemplo, NO NULL. Luego, después de la lista de columnas, define una restricción a nivel de tabla que define las reglas para los datos en la tabla. Después de eso, especifique una tabla existente de la que herede la nueva tabla. Significa que la nueva tabla contiene todas las columnas de la tabla existente y las columnas definidas en la CREATE TABLEdeclaración. Esta es una extensión de PostgreSQL para SQL.

SELECT INTO La instrucción SELECT INTO PostgreSQL le permite crear una nueva tabla e inserta datos devueltos por una consulta. Las nuevas columnas de la tabla tienen nombres y tipos de datos asociados con las columnas de salida de la cláusula SELECT. A diferencia de la declaración SELECT, la declaración SELECT INTO no devuelve datos al cliente. Lo siguiente ilustra la sintaxis de la declaración SELECT INTOPostgreSQL : SELECT column_list INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table_name FROM

table_name WHERE condition;

CREATE TABLE AS La declaración CREATE TABLE AS crea una nueva tabla y la llena con los datos devueltos por una consulta. A continuación se muestra la sintaxis de la declaración CREATE TABLE AS: CREATE TABLE new_table_name AS query;

En esta sintaxis: • •

Primero, especifique el nuevo nombre de la tabla después de la CREATE TABLEcláusula. En segundo lugar, proporcione una consulta cuyo conjunto de resultados se agregue a la nueva tabla después de la ASpalabra clave.

SEQUENCE Por definición, una secuencia es una lista ordenada de enteros. Los pedidos de números en la secuencia son importantes. Por ejemplo, {1,2,3,4,5}y {5,4,3,2,1}son secuencias completamente diferentes. Una secuencia en PostgreSQL es un objeto vinculado a un esquema definido por el usuario que genera una secuencia de enteros basada en una especificación especificada. Para crear una secuencia en PostgreSQL, usa la instrucción CREATE SEQUENCE. ALTER TABLE Para cambiar la estructura de la tabla existente, utiliza la instrucción ALTER TABLE PostgreSQL . La sintaxis de la ALTER TABLE es la siguiente:

ALTER TABLE table_name action;

PostgreSQL proporciona muchas acciones que le permiten: • • • •

Agregue una columna , suelte una columna , cambie el nombre de una columna o cambie el tipo de datos de una columna . Establecer un valor predeterminado para la columna. Agregar una restricción CHECK a una columna. Renombrar una tabla.

8. RESTRICCIONES DE LA BASE DE DATOS PRIMARY KEY Una clave primaria es una columna o un grupo de columnas que se utilizan para identificar una fila de forma exclusiva en una tabla. Usted define claves primarias a través de restricciones de clave primaria. Técnicamente, una restricción de clave primaria es la combinación de una restricción no nula y una restricción ÚNICA . Una tabla puede tener una y solo una clave primaria. Es una buena práctica agregar una clave primaria a cada tabla. Cuando agrega una clave primaria a una tabla, PostgreSQL crea un índice de árbol B único en la columna o un grupo de columnas utilizado para definir la clave primaria. Definir clave primaria al crear la tabla; Normalmente, agregamos la clave primaria a una tabla cuando definimos la estructura de la tabla usando la instrucción CREATE TABLE . CREATE TABLE TABLE ( column_1 data_type PRIMARY KEY, column_2 data_type, … );

FOREIGN KEY

Una clave externa es un campo o grupo de campos en una tabla que identifica de forma exclusiva una fila en otra tabla. En otras palabras, una clave foránea se define en una tabla que hace referencia a la clave primaria de la otra tabla. La tabla que contiene la clave externa se denomina tabla de referencia o tabla secundaria . Y la tabla a la que hace referencia la clave externa se denomina tabla referenciada o tabla primaria . Una tabla puede tener múltiples claves foráneas dependiendo de sus relaciones con otras tablas. En PostgreSQL, define una clave externa a través de una restricción de clave externa. Una restricción de clave externa indica que los valores en una columna o un grupo de columnas en la tabla secundaria coinciden con los valores en una columna o un grupo de columnas de la tabla primaria. Decimos que una restricción de clave externa mantiene la integridad referencial entre las tablas hijo y padre. CHECK Constraint Una restricción CHECK es un tipo de restricción que le permite especificar si un valor en una columna debe cumplir un requisito específico. La restricción CHECK utiliza una expresión booleana para evaluar los valores antes de insertarlos o actualizarlos en la columna. Si los valores pasan la verificación, PostgreSQL insertará o actualizará estos valores en la columna. UNIQUE Constraint A veces, desea asegurarse de que los valores almacenados en una columna o un grupo de columnas sean únicos en toda la tabla, como la dirección de correo electrónico y el nombre de usuario. PostgreSQL le proporciona la restricción UNIQUE para asegurarse de que la unicidad de los datos se mantenga correctamente. Con la restricción UNIQUE, cada vez que inserta una nueva fila , PostgreSQL verifica si el valor ya está en la tabla. Si encuentra que el nuevo valor ya está allí, rechaza el cambio y emite un error. El mismo proceso se lleva a cabo para actualizar los datos existentes . Cuando agrega una restricción UNIQUE a una columna o grupo de columnas, PostgreSQL creará un índice único en la columna respectiva o en un grupo de columnas automáticamente.

NOT-NULL CONSTRAINT En la teoría de bases de datos, NULL es información desconocida o faltante. El valor NULL es diferente de una cadena vacía o número cero. Por ejemplo, puede pedirle a una persona una dirección de correo electrónico; si no lo sabe, usa el valor NULL para insertarlo en la columna de correo electrónico. Esto indica que la información en el momento de la inserción es desconocida. En caso de que la persona no tenga ninguna dirección de correo electrónico, puede actualizarla a una cadena vacía. El valor NULL es muy especial. Por ejemplo, NULL no es igual a nada, incluso NULL. Para verificar si un valor es NULL o no, utilice el operador booleano IS NULL o IS NOT NULL. La expresión NULL = NULL devuelve NULL. PostgreSQL proporciona la restricción no nula para aplicar una columna que no debe aceptar valores NULL. Significa que cada vez que inserte o actualice datos, debe especificar un valor que sea diferente del valor NULL. 9. ADMINISTRACION DE ESQUEMA DE BASE DE DATOS SCHEMA En PostgreSQL, un esquema es un espacio de nombres que contiene objetos de bases de datos con nombre, como tablas, vistas , índices , tipos de datos, funciones y operadores.

Para acceder a un objeto de un esquema, califica su nombre con el nombre del esquema como prefijo: schema_name.object_name

O puede establecer una ruta de búsqueda que incluya el esquema. Cubriremos la ruta de búsqueda del esquema más adelante. Una base de datos puede contener uno o varios esquemas, mientras que cada esquema pertenece a una sola base de datos. Dos esquemas pueden tener diferentes objetos que comparten el mismo nombre. ¿Por qué necesitas usar esquemas?

• •

Los esquemas le permiten organizar los objetos de la base de datos, por ejemplo, tablas en grupos lógicos para hacerlos más manejables. Los esquemas permiten que varios usuarios usen una base de datos sin interferir entre sí.

CREATE SCHEMA La declaración CREATE SCHEMA le permite crear un nuevo esquema en la base de datos actual. Lo siguiente ilustra la sintaxis de la declaración CREATE SCHEMA: CREATE SCHEMA [IF NOT EXISTS] schema_name;

En esta sintaxis: • • •

Primero, especifique el nombre del esquema después de las palabras clave CREATE SCHEMA. El nombre del esquema debe ser único dentro de la base de datos actual. Segundo, opcionalmente, use IF NOT EXISTS para crear condicionalmente el nuevo esquema solo si no existe. Intentar crear un nuevo esquema que ya exista sin usar la opción IF NOT EXISTS dará como resultado un error. Tenga en cuenta que para ejecutar la declaración CREATE SCHEMA, debe tener el privilegio CREATE en la base de datos actual.

ALTER SCHEMA La declaración ALTER SCHEMA le permite cambiar la definición de un esquema . Por ejemplo, puede cambiar el nombre de un esquema de la siguiente manera: ALTER SCHEMA schema_name RENAME TO new_name;

En esta sintaxis: • • •

Primero, especifique el nombre del esquema cuyo nombre desea cambiar después de las palabras clave ALTER SCHEMA. En segundo lugar, especifique el nuevo nombre del esquema después de las palabras clave RENAME TO. Tenga en cuenta que para ejecutar esta declaración, debe ser el propietario del esquema y debe tener el privilegio CREATE de la base de datos.

DROP SCHEMA La DROP SCHEMA quita un esquema y todos sus objetos de una base de datos. Lo siguiente ilustra la sintaxis de la declaración DROP SCHEMA: DROP SCHEMA [IF EXISTS] schema_name [ CASCADE | RESTRICT ];

En esta sintaxis: • • •

Primero, especifique el nombre del esquema del que desea eliminar después de las palabras clave DROP SCHEMA. En segundo lugar, use la opción IF EXISTS para eliminar condicionalmente el esquema solo si existe. En tercer lugar, use CASCADE para eliminar el esquema y todos sus objetos y, a su vez, todos los objetos que dependen de esos objetos. Si desea eliminar el esquema solo cuando está vacío, puede usar la opción RESTRICT. Por defecto, PostgreSQL usa RESTRICT.

Para ejecutar la declaración DROP SCHEMA, debe ser el propietario del esquema que desea eliminar o un superusuario. 10. EXPRESIONES CONDICIONALES Y OPERADORES CASE La expresión CASE PostgreSQL es la misma que la declaración IF/ELSE en otros lenguajes de programación. PostgreSQL proporciona dos formas de las expresiones CASE. Lo siguiente ilustra la forma general de la declaración CASE: CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 [WHEN ...] [ELSE result_n] END

En esta forma general, cada condición es una expresión que devuelve un valor booleano, ya sea trueo false. Si la condición se evalúa como verdadera, la expresión CASE devuelve el resultado correspondiente a la condición y todas las demás CASEramas no se procesan en absoluto. Si todas las condiciones se evalúan como falsas, la expresión CASE devolverá el resultado en la parte ELSE. Si omite la cláusula ELSE, la expresión CASE devolverá nulo. Tenga en cuenta que todas las expresiones de resultados deben tener tipos de datos que puedan ser convertibles a un solo tipo de datos, por ejemplo, cadena, numérico y temporal. COALESCE La sintaxis de la función COALESCE es la siguiente: COALESCE (argument_1, argument_2, …);

La función COALESCE acepta un número ilimitado de argumentos. Devuelve el primer argumento que no es nulo. Si todos los argumentos son nulos, la función COALESCE devolverá nulo.

La función COALESCE evalúa los argumentos de izquierda a derecha hasta que encuentra el primer argumento no nulo. Todos los argumentos restantes del primer argumento no nulo no se evalúan. La función COALESCE proporciona la misma funcionalidad NVL a la función IFNULL proporcionada por el estándar SQL. MySQL tiene la función IFNULL , mientras que Oracle proporciona la función NVL. NULLIF La función NULLIF es una de las expresiones condicionales más comunes proporcionadas por PostgreSQL. A continuación se ilustra la sintaxis de la función NULLIF: NULLIF(argument_1,argument_2);

La función NULLIF devuelve un valor nulo si argument_1es igual a argument_2, de lo contrario, devuelve argument_1. CAST Hay muchos casos en los que desea convertir un valor de un tipo de datos en otro. PostgreSQL le proporciona el operador CAST que le permite hacer esto. Lo siguiente ilustra la sintaxis de tipo CAST: CAST ( expression AS target_type );

En esta sintaxis: • •

Primero, especifique una expresión que puede ser una constante, una columna de tabla, una expresión que se evalúe como un valor. Luego, especifique el tipo de datos de destino al que desea convertir el resultado de la expresión.

Además de la sintaxis CAST de tipo , puede usar la siguiente sintaxis para convertir un valor de un tipo en otro: expression::type