Gracias por quitar el fondo rojo! 😄
Configurar Postgres
Qué aprenderás sobre PostgreSQL
¿Qué es Postgresql?
Instalación y configuración de la Base de Datos
Interacción con Postgres desde la Consola
PgAdmin: Interacción con Postgres desde la Interfaz Gráfica
Archivos de Configuración
Comandos más utilizados en PostgreSQL
Presentación del Proyecto
Tipos de datos
Diseñando nuestra base de datos: estructura de las tablas
Jerarquía de Bases de Datos
Gestión de la información en bases de datos
Creación de Tablas
Particiones
Creación de Roles
Llaves foráneas
Inserción y consulta de datos
Inserción masiva de datos
Generar consultas avanzadas
Cruzar tablas: SQL JOIN
Funciones Especiales Principales
Funciones Especiales Avanzadas
Vistas
PL/SQL
Triggers
Integrar bases de datos con servicios externos
Simulando una conexión a Bases de Datos remotas
Transacciones
Otras Extensiones para Postgres
Implementar mejores prácticas
Backups y Restauración
Mantenimiento
Introducción a Réplicas
Implementación de Réplicas en Postgres
Otras buenas prácticas
Cierre del curso
No tienes acceso a esta clase
¡Continúa aprendiendo! Únete y comienza a potenciar tu carrera
Aportes 66
Preguntas 3
Gracias por quitar el fondo rojo! 😄
Esta instruccion nos permite especificar que debemos hacer en caso de un conflicto.
Ejemplo: Imaginamos que realizamos una consulta donde el id ya ha sido utilizado. Podemos especificar que en ese caso, actualize los datos.
INSERT INTO pasajero (id, nombre, direccion_residencia, fecha_nacimiento)
values (1, '', '','2010-01-01')
ON CONFLICT (id) DO
UPDATE SET
nombre = '', direccion_residencia='', fecha_nacimiento='2010-01-01';
Returning nos devuelve una consulta select de los campos sobre los que ha tenido efecto la instruccion.
Ejemplo: Queremos saber cual es el id que le fue asignado a un dato insertado.
INSERT INTO tren (modelo, capacidad)
VALUES('modelo x', 100)
RETURNING id;
/*
Opcionalmente tambien puedes solicitar todos los campos o alguno de ellos
*/
INSERT INTO tren (modelo, capacidad)
VALUES('modelo x', 100)
RETURNING id;
INSERT INTO tren (modelo, capacidad)
VALUES('modelo x', 100)
RETURNING id, capacidad;
Las funciones like y ilike sirven para crear consultas a base de expresiones regulares.
Like considera mayusculas y minusculas, mientras que ilike solo considera las letras.
Ejemplo: Busquemos a los pasajeros con nombre que terminen con la letra o
-- Usando LIKE
SELECT * FROM PASAJERO
WHERE pasajero.nombre LIKE '%O'
-- No devulve nada, porque ningun nombre terminara con una letra mayuscula
-- Usando ILIKE
SELECT * FROM PASAJERO
WHERE pasajero.nombre LIKE '%O'
-- Devolvera los nombres que terminen con o, independiente si es mayuscula o minuscula.
Permite hacer comprobacion de valores especiales como null
Ejemplo: Consultemos a todos los usuarios que tengan como direccion_residencia NULL
-- IS
SELECT * FROM PASAJERO
WHERE pasajero.nombre IS null;
Ahora a los que si tengan la direccion_recidencia con algun valor
-- IS NOT
SELECT * FROM PASAJERO
WHERE pasajero.nombre IS NOT null;
La función RETURNING es fundamental en la creación y uso de Stored procedure transaccionales. Al momento de insertar información con id autoincremental, con RETURNING podemos obtener dicho valor e insertarlo o referenciarlo en otras tablas.
No lo mencionó el profesor bien en esta clase a mi parecer, pero investigando un poco, cuando haya un conflicto de id con el INSERT, podemos actualizar la tupla correspondiente con los nuevos valores usando la palabra EXCLUDED, de la siguiente manera:
INSERT INTO tren
VALUES(1, 'Modelo modificado', 1000)
ON CONFLICT(id)
DO UPDATE SET modelo = EXCLUDED.modelo, capacidad = EXCLUDED.capacidad;
Porque el profesor escribió a mano los nuevos valores de actualización en el SET, y eso no tiene ningún sentido en el caso de que esta actualización de tuplas a partir del conflicto de id sea ya un proceso automatizado.
Al fin se fue el fondo rojo 😄
Funciones especiales
-- Insercion de un dato que ya existe, no pasa nada
INSERT INTO public.estacion(id, nombre, direccion)
VALUES (1, 'Nombre', 'Dire')
ON CONFLICT DO NOTHING;
-- Insercion de un dato que ya existe, te cambia los campos de nombre y direccion
INSERT INTO public.estacion(id, nombre, direccion)
VALUES (1, 'Nombre', 'Dire')
ON CONFLICT (id) DO UPDATE SET nombre = 'Nombre', direccion = 'Dire';
-- Insertara una tupla y mostrara la tupla
INSERT INTO public.estacion(nombre, direccion)
VALUES ('RETU', 'RETDIRE')
RETURNING *;
-- %: Uno o cualquier valor
-- _: Un valor
SELECT nombre FROM public.pasajero
WHERE nombre LIKE 'o%';
-- buscamos sin importar mayusculas o minusculas
SELECT nombre FROM public.pasajero
WHERE nombre ILIKE 'o%';
-- si una estacion o tren tiene un valor nulo
SELECT * FROM public.tren
WHERE modelo IS NULL;
Mi resumen
Existen 4 funciones especiales que nos ayudarán en nuestro día a día estas son
ON CONFLICT DO: Es una especie de sobre escritura sobre algo que ya este creado “Como un UPDATE”
RETURNING: Muestra en pantalla el último cambio hecho
LIKE / ILIKE: Busqueda por similitudes la diferencia entre ambas es que like busca en minusculas y ilike busca mayusculas/minisculas
IS / IS NOT: comparacion para atributos especiales como el NULL
ON CONFLICT DO se me parece al try: except: de python.
--/////////////////
--19.Funciones Especiales Principales
--/////////////
--ON CONCLICT DO
SELECT * FROM estacion;
-- ON CONFLICT DO NOTHING
--Sí el registro existe, no hace nada pero sí el registro no existe lo crea
INSERT INTO public.estacion (id,nombre,direccion)
VALUES (350,'xxx','xxx')
ON CONFLICT DO NOTHING;
SELECT * FROM estacion;
-- ON CONFLICT DO UPDATE SET
--Sí el registro existe lo actuliza, sí no existe lo crea
INSERT INTO public.estacion (id,nombre,direccion)
VALUES (350,'xxx','xxx')
ON CONFLICT (id) DO
UPDATE SET id = 102 ,nombre = 'San francisco ',direccion = '46 howlang';
SELECT * FROM estacion;
--RETUNING
-- RETURNING * | RETURNING name_column
--Una vez insertamos el valor este no los devuelve muy útil para no usar un SELECT.
INSERT INTO public.estacion (nombre,direccion)
VALUES ('New York Station',' 49 Muir Way')
RETURNING *
--IS/IS NOT
--IS/IS NOT, nos permite comparar tipos de datos que no son estándar o son objetos.
--NULL es un tipo de dato NO ESTÁNDAR
SELECT * FROM public.estacion
WHERE estacion IS NOT NULL;
Compañeros en este link se explica muy bien la primer función. Se entiende la diferencia entre update y on confilct do.
Hasta que por fin le quitó ese color!!! 😄
Que hermoso ya quiero usar únicamente postgreSQL
Para ser precisos: LIKE / ILIKE / IS / IS NOT
son operadores, no funciones.
Información resumida de esta clase
#EstudiantesDePlatzi
Dentro de Postgre tenemos una lista de funciones especiales y que nos ayudan para desarrollar más rápido .
Con NOT e IS NOT es fácil comprobar si tenemos espacios vacíos en nuestras bases de datos.
Para actualizar datos en las tablas podemos usar ON CONFLICT DO
El RETURNING nos ayuda cuando hacemos inserciones y queremos saber que id se creo sin necesitar un SELECT
lIKE nos ayuda a encontrar datos con cierto carácter e ILKE no tiene en cuenta mayúsculas ni minúsculas
No se si alguien lo ha dicho pero:
si % está al principio (’%n’), busca los datos que comiencen con el dicho valor.
Si % está el final (‘n%’), busca los valores que terminen con ese valor.
En la práctica los conflictos con ID’s son muy pero muy raros, recuerda que todo empieza en el diseño que involucra a un equipo multidisciplinario para cubrir las tareas especificas en esta etapa.
El ID es un identificador de registro, pero para que esos atributos de ese registro tengan vida real necesitan un atributo único (que no es el ID!) como sería la identificación fiscal, personal, o de seguridad social por ejemplo, en este caso el conflicto desde el frontend donde el usuario tiende a cargar de nuevo algo existente porque no sigue las buenas practicas NO HACER NADA (NOTHING) MAS SI AVISAR (RETURNING) DONDE ESTA LA DUPLICACION sería de mucha utilidad, PERO UN UPDATE sería romper con las reglas de diseño de un buen CORE de aplicación
ON CONFLICT (atributo_sujeto_NumeroIdentificacion) DO NOTHING
RETURNING atributo_sujeto_NombreIdentificacion;
El LIKE se puede usar con números, previamente pasados a string. Usando por ejemplo “::text”.
De esta manera se pueden hacer búsquedas bastante particulares como por ejemplo encontrar un número que empiece con 2 y tenga al menos 3 dígitos.
SELECT numero FROM tabla_numeros WHERE numero::text LIKE '2_%_%'
https://www.tutorialspoint.com/postgresql/postgresql_like_clause.htm
a la hora de actualizar , prefiero mejor usar el update tradicional y no el ON CONFLICT
Revisando un poco, el ON CONFLICT DO hace lo que el MERGE hace en Oracle 😄
Interesante.
PostgreSQL me está gustando cada vez más
Lo único que veo de diferente entre UPDATE y CONFLICT ON es que UPDATE modifica un dato dependiendo de una condición y CONFLICT ON lo hace atraves de error. Puede sonar igual porque las actualizaciones lo hacemos dependiendo del ID que es único, pero pensemos en el momento en que debamos modificar por ejemplo cambiar PESOS COLOMBIANOS a COP ahí utilizamos UPDATE, y ahora pensemos en donde insertemos y en el espacio de moneda, tu no tienes ideas de que si es PESOS COLOMBIANOS o COP, entonces para estar prevenido de que no vas a perder el tiempo en la línea 500 haces de tu INSERT lo prevines con esto.
En otras palabras CONFLICT ON sirve para prevenir errores
La función de returning me hubiera ayudado mucho en el trabajo, siempre volvia a leer los registros con la condicional con todos los datos de los campos de la fila insertada era poco eficiente muy tardado y generaba código repetitivo.
Muy interesante, estas fucniones estan en otros motores como Sql Sever, pero tienen un difenete nombre en algunas ocasiones.
INSERT INTO public.estacion(
id, nombre, direccion)
VALUES (1, 'Estacion Cambio', 'Calle 5 # 68');
INSERT INTO public.estacion(
id, nombre, direccion)
VALUES (1, 'Estacion Cambio', 'Calle 5 # 68')
ON CONFLICT DO NOTHING;
INSERT INTO public.estacion(
id, nombre, direccion)
VALUES (1, 'Estacion Cambio', 'Calle 5 # 68')
ON CONFLICT (ID) DO UPDATE SET nombre = 'Estacion Cambio' , direccion= 'Calle 5 # 68';
INSERT INTO public.estacion(
nombre, direccion)
VALUES ( 'Estacion Nueve', 'Calle 9 # 99')
RETURNING *;
SELECT *
FROM pasajero
WHERE nombre LIKE 'o%';
SELECT *
FROM pasajero
WHERE nombre ILIKE 'o%';
SELECT *
FROM tren
WHERE modelo IS NULL;
SELECT *
FROM tren
WHERE modelo IS NOT NULL;
IS/IS NOT, nos permite comparar tipos de datos que no son estándar o son objetos.
… si de pronto está molestado este colo ROJO…
Gracias por quitar el fondo Rojo
LIKE es case sensitive, es decir, si importa si la búsqueda es en mayúsculas o minúsculas; mientras que ILIKE es al contrario, No es case sensitive, por tanto, muestra todos los que estén en mayúsculas y minúsculas.
Esta fuen la segunda clase mas importante que vi, en todo el curso, ya que me enseño cosas para mejorar mis consultas, 1000 likes
Funciones especiales principales:
IS / IS NOT
LIKE Y ILIKE
ON CONFLICT DO
Esto es más una pregunta pero lo pongo en aportes para que pueda ser más visual. ¿Cúal es la dieferencia entre ON CONFLICT DO y UPDATE?. Realicé el ejercicio usando las dos opciones y el resultado es el mismo.
INSERT INTO public.estaciones(id, nombre, direccion)
VALUES (1, 'nombre','dire')
ON CONFLICT(id) DO UPDATE SET nombre = 'nombre', direccion = 'dire';
UPDATE public.estaciones
SET nombre = 'surname', direccion = 'new address'
WHERE id=2;
Si quieren ver más sobre cómo se utilizan las expresiones regulares en Postgresql, les recomiendo leer la documentación oficial.
https://www.postgresql.org/docs/9.3/functions-matching.html
woooo, genial. esto esta brutal
Si de pronto esta molestando el color rojo, nooooo, que va
jaja
Uso de función ILIKE, esta función es igual que LIKE con la diferencia de que ILIKE no distingue entre mayúsculas y minúsculas.
Uso de la función RETURNING *; con * se indica que regrese todos los campos, pero de igual forma se pueden especificar los campos a retornar.
Uso de función ON CONFLICT DO pero para realizar un UPDATE en lugar de no hacer nada. INSERT INTO public.estacion(id, nombre, direccion) VALUES (1, 'Nombre', 'Dire') ON CONFLICT (id) DO UPDATE SET nombre = 'Nombre', direccion = 'Dire';
Uso de función ON CONFLICT DO NOTHING
El RETURNING no es sólo para el INSERT, sirve también para UPDATE y DELETE.
Intentar insertar un valor donde ya existe un ID:
INSERT INTO public."Pasajeros"(
id, nombre, direccion_residencia, fecha_nacimiento)
VALUES (1, 'Miguel', 'Algun lado', '1998-10-22')
ON CONFLICT(id) DO UPDATE SET nombre = 'Miguel', direccion_residencia='Algun lado', fecha_nacimiento='1998-10-22';
Ver qué información acabamos de insertar:
INSERT INTO public."Pasajeros"(
nombre, direccion_residencia, fecha_nacimiento)
VALUES ('Ximena', 'Por ahí', '1999-11-15')
RETURNING id, nombre, direccion_residencia, fecha_nacimiento;
SELECT nombre
FROM public."Pasajeros"
WHERE nombre ILIKE 'x%';
-- `%` -> cualquier caracter
-- LIKE case sensitive
-- ILIKE case insensitive
ON CONFLICT DO; RESUELVE UN CONFLICTO DE INSERCIÓN PARA QUE PERMITA LA ACTUALIZACIÓN. AUNQUE NO LE VEO CASO PORQUE PARA ESO ESTÁ UPDATE
RETURNING, DEVUELVE EL ID SERIAL
LIKE / ILIKE. MINIEXPRESIONES DE BUSQUEDA LIKE ‘%%’
% TODO LO QUE ESTÁ ADELANTE O DETRAS DEL TEXTO
_ OMITE ESE CARACTER ES COMO UN COMDIN
IS / IS NOT ES PARA BUSCAR VALORES NULOS
LIKE/ILIKE nos permite realizar búsquedas al estilo de expresiones regulares dónde podemos probar buscar nombres por un carácter en específico que comience, termine o se encuentre entre una cadena de carácteres.
ON CONFLICT DO, permite realizar una actualización en un registro si este se encuentra, sí no se encuentra se insertará, https://www.postgresqltutorial.com/postgresql-upsert/
muy buena explicación
Hacer un RETURNING después del UPDATE SET ejecutado por el command ON CONFLICT DO:
INSERT INTO public.estacion(
id, nombre, direccion)
VALUES (1,‘Nombre’,‘Direccion’)
ON CONFLICT (id) DO UPDATE SET nombre = ‘Nombre’,
direccion = 'Direccion’
RETURNING *;
RETURNING, evita realizar una consulta adicional en la base de datos para recopilar los datos CUANDO se realize un INSERT, UPDATE, DELETE, y es especialmente valioso cuando de otro modo sería difícil identificar las filas modificadas de manera confiable.
super sencillo
Principales funciones especiales de PostgreSQL
Para hacer algún cambio sobre la base de datos, primero la desconectamos y después modificamos: Disconnect Server / Properties..
<INSERT INTO estaciones(estacion_id, nombre, direccion)
VALUES (1,'Estacion Central','Ciudad de Mexico')
ON CONFLICT (estacion_id) DO UPDATE SET nombre = 'Estacion Central', direccion = 'Ciudad de Mexico'
RETURNING *;>
ON CONFLICT DO: Para insertar o modificar datos ya existentes.
RETURNING
LIKE & ILIKE:
IS & IS NOT: Para saber si un dato es Nulo o no.
Desconectar el servidor para recién poder cambiar las propiedades del servidor y luego volver a conectar
¿Cómo podría aplicar ON CONFLICT DO pero para muchos datos?
.
A veces cuando intento insertar muchos datos me muestra este tipo de error:
__
ERROR: insert or update on table "viajes" violates foreign key constraint "viajes_trayectos_fkey" DETAIL: Key (id_trayectos)=(8) is not present in table "trayectos". SQL state: 23503
Esta es una diferencia con MySQL, el comando LIKE en MySQL trae la consulta sin importar Mayúsculas o minúsculas.
SELECT nombre
FROM public.pasajero
WHERE nombre ILIKE ‘o%’;
SELECT *
FROM public.tren
WHERE modelo IS NULL;
SELECT *
FROM public.tren
WHERE modelo IS NOT NULL;
INSERT INTO public.estacion(
id, nombre, direccion)
VALUES (1, ‘Douglas’, ‘Jr. independencial 1552’)
ON CONFLICT(id) DO UPDATE SET nombre = ‘Douglas’, direccion = ‘Jr. independencial 1552’;
INSERT INTO public.estacion(
id, nombre, direccion)
values (102, ‘Nombre’, ‘Dire’)
ON CONFLICT(id) DO UPDATE SET nombre = ‘Nombre’, direccion = ‘Dire’;
select * from estacion order by id asc;
INSERT INTO public.estacion(
nombre, direccion)
values ( 'Ret', 'RETDire')
RETURNING*;
SELECT nombre
from pasajero
WHERE nombre ILIKE 'o%';
--LIKE no discrimina entre mayuscula, ILIKE si
SELECT *
from tren
WHERE modelo IS NOT NULL ;
Buena Clase
Les comparto la actualización usando los valores ingresados sin volverlos a escribir en código.
INSERT INTO tren
VALUES(1, 'Modelo modificado', 1000)
ON CONFLICT(id)
DO UPDATE SET modelo = EXCLUDED.modelo, capacidad = EXCLUDED.capacidad;
Lo que entiendo es que cuando usas el ON CONFLICT DO, estás forzando a hacer la inserción, que en realidad esa una actualización, porque ya existe la id.
Los aportes, preguntas y respuestas son vitales para aprender en comunidad. Regístrate o inicia sesión para participar.