Preguntas y Respuestas

Clase 22 de 22 • Curso de PostgreSQL 2017

Elmer John Pérez Espinoza Pérez Espinoza

student•

Gracias por la información pero mmmm no cubrió mis expectativas para mis propósitos, creo que deben estructurar mejor el curso, básico, intermedio avanzado, administración, programación...

Tecno Motum

student•

tienes toda la razon, tampoco cubrieron mis expectativas, pense que era un curso no una “vista” de lo que podia hacer. no explicaron ni pg_backup ni pg_restore ni la parte de consultas anidadas con alias.

Cesar David Ramírez Dimaté

student•

Este curso debería llamarse Curso básico de PostgreSQL. Se tocaron muchos temas por encima, no hubo preparación del curso a gran escala (en cuanto a uso de presentaciones y ejercicios más detallados) y claramente es necesaria la actualización a la nueva metodología que Platzi maneja actualmente. Independiente si no existen cambios de la versión 9.4 a la 10, es necesario que mejoren los siguientes puntos:

Definir bien cómo se crea un procedimiento almacenado (anónimo, definido) y cómo llamarlo.
Cómo realizar una conexión remota a la base de datos según los archivos de configuración.
Mostrar la interfaz de pgAdmin, ya sea la versión 3 o 4.
Explicación básica de cómo ejecutar una tarea programada para un procedimiento almacenado.
Explicar más en detalle funciones de agregación de PostgreSQL para manipular datos.
Explicar más en detalle cómo se manejan los privilegios y que pasa si un rol intenta ejecutar algo que no tiene permitido hacer.
Crear una plantilla personalizada desde cero y aplicarla en una base de datos nueva.
Explicar bien sobre el proceso de backup y restore.
Cómo realizar un rollback.
Explicar en detalle cómo instalar PostGIS ya que pide varias librerias previas para su uso.

Jose Eduardo Nanni

student•

Totalmente de acuerdo!

Mario Alejandro Cortez Salinas

student•

Estoy de acuerdo contigo, creo que a muchos nos gusta platzi por su metodología y en este curso por ser de hace varios años lo sentí muy parecido a lo que puedes encontrar en otras plataformas.

Ortiz Ordoñez Jaime Javier

student•

Gracias pero el curso no cumplio mis espectativas....trabajo con windows y no aprendí nada. El profe muy bueno y sabe mucho pero a mi no me sirvio este curso.

Angel Luis Ramos Aquino

student•

mira este blog, es muy bueno.

Beto Mendez Vilca

student•

Este curso me parece que abarca lo basico pero igual no cumplio mis espectativas

Nadir Antonio Soza Solis

student•

Mi resumen del curso…

##Leer tamaño de cada uno de los schemas de la BD

#Opcion 1
select schemaname,
pg_size_pretty(sum(size)) size,
pg_size_pretty(sum(external_size)) external_size
from (
SELECT
schemaname,
relname astable,
pg_total_relation_size(relid) assize,
pg_total_relation_size(relid) - pg_relation_size(relid) as external_size
FROM pg_catalog.pg_statio_user_tables) as sub
group by schemaname;

#Opcion 2

SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS SIZE
FROM pg_database;

CREAR ROLES

#Roles con password simple
CREATE ROLE nombre LOGIN PASSWORD ‘password’;

#Roles con password cifrado
CREATE ROLE nombre LOGIN ENCRYPTED PASSWORD ‘password’;

#Eliminar roles
DROP ROLE nombre;

#Definir vigenicia o validez a los roles
CREATE ROLE nombre LOGIN ENCRYPTED PASSWORD ‘password’ VALID UNTIL ‘infinity’; --Definicion por defecto para todos los roles
CREATE ROLE nombre LOGIN ENCRYPTED PASSWORD ‘password’ VALID UNTIL ‘2018-8-1 00:00’; --Rol vigente hasta la fecha indicada

#Consultar todos los roles
SELECT * FROM pg_roles;

#Asignar un rol a otro rol
– rol1 hereda permisos de rol2
GRANT rol1 TO rol2;
– Debido a que los permisos de SUPERUSER no pueden ser heredados para poder utilizar esos permisos que pertenecen a mi rol padre
– debo moverme de rol con el comando:
SET ROLE rol2

#Asignar Privilegios
GRANT nombre_privilegio TO Rol WITH GRANT OPTION
Ejm: GRANT ALL ON ALL TABLES IN SCHEMA nombre_schema TO Rol - Asignar todos los privilegios
GRANT SELECT ON ALL TABLES IN SCHEMA nombre_schema TO Rol - Asignar privilegios para selecionar

##CREACION DE BASES DE DATOS
CREATE DATABASE nombre_db;

#Crear DB a partir de una plantilla
– La plantilla por defecto de PostgreSQL es template1
CREATE DATABASE nombre_db TEMPLATE nombre_template

#Consultar toda la informacion de las BD de PostgreSQL
SELECT * FROM pg_database;

##CREACION DE SCHEMAS
CREATE SCHEMA nombre_schema

#CREACION DE SECUENCIAS
CREATE SECUENCE nombre_secuencia;

#CONSULTAR SECUENCIAS
– Obtener el siguiente valor de la secuencia
SELECT NEXTVAL(‘nombre_secuencia’);
– Obtener el valor actual de la secuencia
SELECT CURRVAL(‘nombre_secuencia’);
– Setea un valor especifico a la secuencia
SELECT SETVAL(‘nombre_secuencia’,5);

#FUNCIONES PARA TEXTO
– Completa a la izquierda una cadena de texto con el caracter de relleno si no cumple con la longitud.
lpad(‘txt’,longitud,relleno);
– Completa a la derecha una cadena de texto con el caracter de relleno si no cumple con la longitud.
rpad(‘txt’,longitud,relleno);
– Elimina los espacios en blanco dentro de una cadena.
trim(’ a ‘);
– Elimina los espacios en blanco a la izquierda dentro de una cadena.
ltrim(’ a’);
– Elimina los espacios en blanco a la derecha dentro de una cadena.
trim(‘a ‘);
– Para concatenar texto
||
–Funcion para separar una cadena y mostrarlas en partes separadas
split_part(‘12-1235-122’,’-’,2);

FUNCIONES PARA ARREGLOS
–El indexador de arreglos en PostgreSQL inicia desde 1 no desde 0 como en algunos lenguajes
–Transforma una cadena de texto en un arreglo, el punto en este caso es el separador. la consulta retornara {aa,bbb,cccc}
-string_to_arrays(‘aa.bbb.cccc’,’.’)

–Creacion de un arreglo
-SELECT ARRAY[2014,2015,2016] as Ar
–Creacion de un arreglo desde los datos de una tabla
-SELECT ARRAY(SELECT DISTINCT genero FROM DATOS) as Arr
–Castear un string a una arreglo de texto con ::
-SELECT ‘{uno,dos,tres}’::text[] as x
–Consultar un elemento especifico dentro del arreglo
-SELECT c[1] FROM(SELECT ‘{uno,dos,tres}’::text[] as c) as Y
–Consultar rango de elementos especifico dentro del arreglo
-SELECT c[2:3] FROM(SELECT ‘{uno,dos,tres}’::text[] as c) as Y
–Devuelve los elementos del arreglo el forma de registros de una columna
-SELECT UNNEST(c[2:3]) FROM(SELECT ‘{uno,dos,tres}’::text[] as c) as Y

– USO DE RANGOS

SELECT * FROM data WHERE ‘[0,6)’::int8range;
–(0 = 6 (apartir del 0 en adelante)
–[0 = 6 (apartir del 0 en adelante pero incluye el 0)
– Query para mostrar los datos de una columna que estan dentro del rango
– Sintaxis @> nombre_columna para saber si esta dentro del rango
SELECT * FROM data WHERE ‘[70,0,71,0)’::numrange @> height;

JSON EN POSTGRESQL
–JSON almacena el documento intacto tal y como lo enviamos a almacenar, respeta los espacios que utilizamos para que la lectura del documento sea mas comoda.
–JSONB alamacena el documento de forma binaria lo cual utiliza menos espacio de memoria.

–Crear una tabla con un tipo de datos JSON
CREATE TABLE profiles(id SERIAL PRIMARY KEY,profile JSON);
INSERT INTO profiles(profile) values(’{{“name”:“Nadir”,“tech”:[“java”,“sql”,“c#”]}’);
SELECT json_extarct_path_text(profile,‘name’);

CREATE TABLE profiles_b(id SERIAL PRIMARY KEY,profile JSONB);
INSERT INTO profiles_b(profile) values(’{{“name”:“Nadir”,“tech”:[“java”,“sql”,“c#”]}’);

SELECT id,profile->>‘name’ from profiles;
SELECT * from profiles WHERE profile->>‘name’=‘Nadir’;

–Convertir la fila de registro de una tabla a formato JSON
-Retorna todas las filas en formato JSON
SELECT row_to_json(data) from data;
-Retorna columnas especificas pero los nombres de las llaves no corresponden a las almacenadas
SELECT row_to_json(row(columna1,columna3)) from data;
-Retorna las columnas especificas con sus llaves correspondientes
SELECT row_to_json(t) FROM (SELECT columna1,columna3 from data) t;
–Retorna las filas de la consulta como un unico documento JSON, no como uno por cada Fila como las consultas anteriores.
SELECT array_to_json(array_agg(row_to_json(t))) FROM (SELECT columna1,columna3 from data) t;

Herramientas para respaldos de POSTGRESQL
–pg_backups
–pg_jobs

Extensiones para postgreSQL
begriffs/postgrest : Proyecto GitHub para API REST
dockyard/postgres_ext-serializers : extension para serealizar con Ruby y Ruby on Rails

POSTGRESQL HSTORE
–Permite almacenar documentos o datos no estructurados. Es una extension que viene instalda pero no viene activa.

-Activar HSTORE
CREATE EXTENSION hstore
-Inhabilitar HSTORE
DROP EXTENSION hstore

–Crea una tabla con una columna hstore
CREATE TABLE hprofiles(id SERIAL PRIMARY KEY,profile hstore);
–Insertamos registros de tipo no estructurado en una columna de tipo hstore.
INSERT INTO hprofiles(profile) VALUES(‘name=>Mario, ruby=>true,postgreSQL=>true’);
–Selecionamos los registros que tienen una llave llamada name en la columna profile.
SELECT profile ->>‘name’ from hprofiles;
–Selecionamos los registros que en la columna profile contine una llave llamada ruby, se utiliza cuando los registros en la columna difieren en sus llaves.
SELECT * FROM hprofiles WHERE (profile->‘ruby’)::boolean;
–Selecionamos los registros que en la columna profile contine una llave llamada nodejs y su valor es true.
SELECT * FROM hprofiles WHERE profile @>‘nodejs=>true’;
–Selecionamos los registros que en la columna profile exista una llave llamada nodejs
SELECT * FROM hprofiles WHERE profile ? ‘nodejs’;
– El signo ?& significa contiene ambas llaves.
SELECT * FROM hprofiles WHERE profile ?& ARRAY[‘nodejs’,‘ruby’];
– El signo ?| significa contiene alguna de las llaves.
SELECT * FROM hprofiles WHERE profile ?| ARRAY[‘nodejs’,‘ruby’];
–Actualiza los registros de la columna profile y le adiciona la llave html5.
UPDATE hprofiles SET profile=profile || ‘html5=>true’::hstore;
–Con la funcion delete eliminamos una llave dentro de la columna profile.
UPDATE hprofiles SET profile= DELETE(profile,‘html5’);
–Retorna un arreglo con todas las llaves que contiene la columna profile.
SELECT akeys(profile) from hprofiles;
–Retorna en columnas todas las llaves que contiene la columna profile.
SELECT DISTINCT skeys(profile) from hprofiles;
–la funcion hstore_to_json transforma una columna de htore a json
SELECT hstore_to_json(profile) from hprofiles

POSTGRESQL POSTGIS
–postgis.net
– No viene activo por defecto en postgreSQL por lo que debemos activar la extension
– Las columnas de tipo geography pueden ser indexadas
– Hay librerias que entienden POSTGIS en java, Ruby on Rails y Python
– CREATE EXTENSION postgis;

CREATE hospitales (id serial PRIMARY KEY,
location geography,
position geometry(POINT, 4326),
name text);

INSERT INTO hospitales (name,location)
VALUES (‘Hostial Asuncion’,ST_POINT(-6.3788,53.2911));

INSERT INTO hospitales (name,location)
VALUES (‘Hostial SUMEDICO’,ST_POINT(-7.3288,43.2911));
–Compara la distancia de un punto respecto a la localizacion de los hospitales
SELECT name, ST_DISTANCE(location,ST_POINT(-6.3788,53.2911)::geography);
–Verifica que hospitales estan en un radio de u punto
SELECT name FROM hospitales WHERE ST_DWithin(location,ST_POINT(-6.2379,53.3411)::geography,1000);
–Retorna la informacion en texto
SELECT name, ST_ASTEXT(location) from hospitales;
– Retorna la informacion en formato JSON
SELECT name, ST_ASGEOJSON(location) from hospitales;
---- Retorna la informacion en formato XML
SELECT name, ST_ASKML(location) from hospitales;

PARA INVESTIGAR
–Funciones de Ventana
–Funciones de Particion
– Commons Table Expresion CTE

Roberto Carlos Terrones Rojas

student•

No fue el curso que me esperaba. Fue un curso de repaso, dirigido para personas que tienen un conocimiento previo. Ojalá lo actualicen y sea un curso para aprender desde cero y se haga uso de la interfaz gráfica. 👎

Nicole Chapaval

Team Platzi•

Hey! Queremos actualizar este curso pronto. Me gustaría mucho saber más sobre los conceptos que no te quedaron claros o si hay algo en particular que crees que faltó.

Diego Forero

Team Platzi•

Un administrador de bases de datos siempre prefiere trabajar sobre la terminal ya que es mucho más rápida y tiene control absoluto de todo lo que hace, el trabajar con Interfaces Gráficas hacen que operaciones como queries sean lentas porque el software tiene que pintar la información.

Michel Carvajal

student•

Me gustó mucho el curso, excelente maestro Mario Chávez, creo que aprendí bastante en poco tiempo, pero en lo personal creo que faltan muchas cosas por ver, por ejemplo me gustaría haber visto algunas de las principales funciones que tiene el pgAdmin.

Concuerdo con otros comentarios que hablan de separar el curso en principiantes, intermedios y avanzados.

Omar Valentín Ortega Martínez

student•

Dependerá del problema a resolver si es mejor utilizar procedimientos almacenados o escribir el código SQL en algún componente del Framework.
Por ejemplo:
Tarea programada: Procedimiento almacenado
Operacion CRUD Simple: código SQL o DML desde componente de software

Luis Ramirez L Luis Luis

student•

Hola... tendrán las ligas a estos videos que el profesor quedo de estar al pendiente dentro de Patzi ???

Javier Insuasti Morales

student•

Se nota que el profe Mario sabe muchisimo de PSQL pero a mi parecer el curso no esta muy estructurado, es algo disperso, pero aprendi muchisimo acerca de las funciones unicas de PSQL

Jordan Lancheros

student•

Me gustaria que hubieran ejemplos de querys complejos, con CTE
como optimizar querys

Daniel Ramírez Mendoza

student•

El profesor se ve que sabe mucho acerca de PostgreSQL, se que el ¨curso¨ fue grabado en vivo, lo cual no es fácil, pero debido a eso le falta estructuración del contenido explicado.

Gilberto Murillo

student•

Muy buen curso, su enfoque en ideas al impacto y uso de este SMBD.

Alexander Silvera

student•

Cuando se va a actualizar el curso ya que tengo las mismas expectativas que los demás compañeros??

Luis Agustin Suaña Jala

student•

Tenia otra expectativa del curso, pero me gusto los temas que se trataron.
Buen final “dejen MySQL, váyanse a PostgreSQL” xD

Alejandro Valenzuela

student•

Buenas, estuvo bueno el curso, para aquellos que no conocen postgreSQL, este seria una introducción. Estaría bueno que lo aclaren que es solo un curso básico y no Profesional porque todo esperamos algo mas elaborado, osea, en profundizar los conceptos mas ejemplos etc.
También quería mencionar sobre el examen final, que estaría bueno que nos permita rever las preguntas una vez contestadas.

Saludos gracias profe!

Carlos Alberto Gonzalez Rembao

student•

Creo que el curso es muy, muy básico, bueno pero basico. falta muchos temas y me gustaría uno que tenga que ver con la administración de la base de datos postgreSQL.

Empezó bien y luego fue bajando.

Usuario anónimo

user•

Muy buen contenido del curso.

Jhon Elvis Ramírez Paredes

student•

Humm el curso no es como lo imaginaba, pensé que seria mas practico y ver el producto como una base de datos de restaurante o libreria

Diego Forero

Team Platzi•

El curso esta enfocado a la administración de bases de datos postgresql, no tanto al lenguaje sql, que es similar en todos los motores de bases de datos. Lo que cambia sobre sql en cada motor es el uso de campos especiales como range field o json field que los tiene postgresql pero no mysql.

Cesar David Ramírez Dimaté

student•

@GOLLUM23, el curso le falta muchísimo ya que se tocaron pinceladas (temas básicos) de muchas cosas. No es pretexto que la última versión no ha cambiado a la impartida en el curso (la 9.4) sino que tanto la metodología y profundidad del curso debió abarcar muchos temas.

Carlos Jimenez

student•

Buenas noches,

Necesito saber como generar backup, como cargar backups,
como cargar un backup creado en mi computador en aws

Hernán Garcia

student•

Hola qué tal Carlos? el comando para hacer backups de la BD en PostgreSQL es:

pg_dump dbname > outfile

Luego en AWS puedes utilizar RDS donde debes utilizar un comando:

psql -h <IP de tu BD> dbname < infile

Si tienes dudas nos avisas,
Saludos!

Enrique Gálvez

student•

Para mi gusto, le falta una parte importante sobre la administración del motor Postrgresql, realización de backups, gestión de las instancias, actualización de una versión a otra (Debian termina ejecutando dos o más instancia consecutivas con puertos distintos), uso en servidores redundados, etc… En fin, la gestión del servidor.

Preguntas y Respuestas

Bienvenidos

Introducción a Postgresql

PostgreSQL: qué es

Lo nuevo en Postgresql

¿Por qué es PostgreSQL?

Entendiendo el Core de Postgresql

Archivos de configuración

Manejo De Conexiones