No tienes acceso a esta clase

隆Contin煤a aprendiendo! 脷nete y comienza a potenciar tu carrera

Curso de PostgreSQL

Curso de PostgreSQL

Oswaldo Rodr铆guez Gonz谩lez

Oswaldo Rodr铆guez Gonz谩lez

Funciones Especiales Principales

19/32
Recursos

Aportes 66

Preguntas 3

Ordenar por:

Los aportes, preguntas y respuestas son vitales para aprender en comunidad. Reg铆strate o inicia sesi贸n para participar.

Gracias por quitar el fondo rojo! 馃槃

<h1>Funciones principales</h1>
  • 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 鈥淐omo 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 (鈥榥%鈥), busca los valores que terminen con ese valor.

En la pr谩ctica los conflictos con ID鈥檚 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,鈥楴ombre鈥,鈥楧ireccion鈥)
ON CONFLICT (id) DO UPDATE SET nombre = 鈥楴ombre鈥,
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 鈥榦%鈥;

SELECT nombre
FROM public.pasajero
WHERE nombre ILIKE 鈥榦%鈥;

  • 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, 鈥楧ouglas鈥, 鈥楯r. independencial 1552鈥)
ON CONFLICT(id) DO UPDATE SET nombre = 鈥楧ouglas鈥, direccion = 鈥楯r. independencial 1552鈥;

  • RETURNING
    INSERT INTO public.estacion(
    nombre, direccion)
    VALUES (鈥楳aglin鈥, 鈥楳z. A Lt. 18鈥)
    RETURNING *;

INSERT INTO public.estacion(
id, nombre, direccion)
values (102, 鈥楴ombre鈥, 鈥楧ire鈥)
ON CONFLICT(id) DO UPDATE SET nombre = 鈥楴ombre鈥, direccion = 鈥楧ire鈥;

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.