Funciones Especiales Principales

Gracias por quitar el fondo rojo! 😄

• ON CONFLICT DO
• RETURNING
• LIKE / ILIKE
• IS / IS NOT

ON CONFLICT DO

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

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;

Like / Ilike

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.

IS / IS NOT

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

• ON CONFLICT DO
• RETURNING
• LIKE / ILIKE
• IS / IS NOT

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

https://www.youtube.com/watch?v=RaK2azzr6wc

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:

• ON CLONFLICT DO = Ayuda a solucionar problemas cuando se quieren insertar o modificar datos en una tabla.
• RETURNING = Devuelve todos los cambios que se han hecho sobre la DB.
• LIKE/ILIKE = Sirve para hacer búsquedas al estilo de expresiones regulares, buscar nombre que empiecen por ciertas palabras o letras.
• IS/ISNOT = Permite comparar dos tipos de datos que no sean standard, como tipo objeto o especiales como null.

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.

Funciones Especiales Principales

• On conflict do -> Nos ayuda a resolver problemas cuando queremos insertar o modificar datos en una tabla
• returning -> Devuelve los resultados de una sentencia
• like / ilike -> Es como una expresión regular
• is / is not -> Nos permite comparar 2 tipos de datos que no sean estándar

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.

• LIKE / ILIKE
  SELECT nombre
  FROM public.pasajero
  WHERE nombre LIKE ‘o%’;

SELECT nombre
FROM public.pasajero
WHERE nombre ILIKE ‘o%’;

• IS / IS NOT

SELECT *
FROM public.tren
WHERE modelo IS NULL;

SELECT *
FROM public.tren
WHERE modelo IS NOT NULL;

• ON CONFLICT DO

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’;

• RETURNING
  INSERT INTO public.estacion(
  nombre, direccion)
  VALUES (‘Maglin’, ‘Mz. A Lt. 18’)
  RETURNING *;

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.