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;

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.

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.

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

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

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

ON CONFLICT DO se me parece al try: except: de python.

Para ser precisos: LIKE / ILIKE / IS / IS NOT son operadores, no funciones.

Hasta que por fin le quitó ese color!!! 😄

Que hermoso ya quiero usar únicamente postgreSQL

… si de pronto está molestado este colo ROJO…

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

• ON CONFLICT DO: Ayuda a solucionar problemas cuando se insertan o modifican datos en una tabla y no se pueda. Despues, se hace la actualizacion correcta. Si por ejemplo se quiere insertar un dato que ya existe, on conflict do ayuda a insertar si lo que realmente se quiere es actualizar.
• RETURNING: Permite devolver todos los cambios que se han hecho sobre la BD. Devolver no es devolver hacia atras, o deshacer, sino retornar la informacion cuando por ejemplo se hace un insert y esto sirve para confirmar que todo quedo bien. Si hay un caso tipo serial, retorna el valor asignado.
• LIKE/ILIKE: Sirve para hacer busquedas al estilo de expresiones regulares donde se puede probar por ejemplo buscar nombres que empiecen por una letra en particular
• IS/IS NOT Permite comparar dos tipos de datos que no son especiales sino que por ejemplo son numericos o alfanumericos. Permite saber si un campo es nulo o no.

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

Se me ocurre que el RETURNING sirve al momento de que se crea un usuario en la UI, mostrar toda su información y pintarla al momento sin tener que recargar la página. Si no que mas bien se estaría actualizando el estado.

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;

más información sobre estas funciones en la documentación oficial de PostgreSQL:

ON CONFLICT DO: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT

RETURNING: https://www.postgresql.org/docs/current/dml-returning.html

LIKE/ILIKE: https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE

IS/IS NOT: https://www.postgresql.org/docs/current/functions-comparison.html#FUNCTIONS-COMPARISON-IS-NULL

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.

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.

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

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

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.

Gracias por quitar el fondo Rojo

En resumen,ON CONFLICT DO RETURNINGse utiliza para manejar conflictos en las inserciones,ILIKE se utiliza para realizar búsquedas de patrones de texto sin distinguir mayúsculas y minúsculas, yIS / IS NOT se utilizan para comparar valores con NULL. Cada uno tiene un propósito específico y se utiliza en diferentes contextos.

funciones especiales de PostgreSQL incluyen:

1. Tipos de Datos Avanzados:
   PostgreSQL permite la creación de tipos de datos personalizados, lo que significa que puedes definir tus propios tipos de datos específicos para tu aplicación.
2. Soporte para JSON y JSONB:
   PostgreSQL ofrece soporte nativo para datos JSON y JSONB, permitiéndote almacenar y consultar datos JSON directamente en la base de datos.
3. HSTORE:
   PostgreSQL tiene un tipo de datos llamado HSTORE que permite almacenar un conjunto de pares clave-valor en una sola columna.
4. Índices Avanzados:
   PostgreSQL ofrece varios tipos de índices avanzados como índices GIN (Generalized Inverted Index) y índices GiST (Generalized Search Tree) que son útiles para consultas complejas.
5. Funciones de Agregación Personalizadas:
   Puedes crear tus propias funciones de agregación personalizadas para realizar operaciones específicas durante las consultas.
6. Funciones Window:
   PostgreSQL soporta funciones de ventana (window functions) que te permiten realizar cálculos a lo largo de un conjunto de filas que están relacionadas con la fila actual.
7. Soporte para Geolocalización:
   PostgreSQL incluye soporte para datos geoespaciales y puede manejar consultas espaciales y de ubicación.
8. Herencia de Tablas:
   PostgreSQL permite la creación de tablas hijas que heredan propiedades de tablas padre. Esto es útil para crear estructuras de tablas complejas y gestionar la herencia de datos.
9. Triggers y Reglas:
   PostgreSQL permite crear triggers (disparadores) que se activan automáticamente en respuesta a ciertos eventos en las tablas. También puedes utilizar reglas para controlar cómo se manejan las consultas en la base de datos.
10. Extensiones:
    PostgreSQL permite el uso de extensiones que proporcionan funcionalidades adicionales y pueden ser creadas por la comunidad de usuarios.
    Estas son solo algunas de las muchas características especiales que ofrece PostgreSQL. La flexibilidad y extensibilidad de PostgreSQL lo convierten en una opción popular para una variedad de aplicaciones y escenarios de uso.
    gpt

La diferencia entre LIKE y ILIKE es que ILIKE no discrimina entre mayúsculas y minúsculas y LIKE si discrimina

ON CONFLICT , RETURNING, LIKE, ILIKE, IS/IS NOT

Mis Ojos estaban al Borde del colapso con ese Rojo jajajaja

19. Funciones Especiales Principales

Principales

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

funciones principales:

ON CONFLICT DO
RETURNING
LIKE / ILIKE
IS / IS NOT

ON CONFLICT DO

La función ON CONFLICT DO es utilizada en PostgreSQL para manejar conflictos en las operaciones de inserción o actualización en una tabla. Por ejemplo, si intentamos insertar un registro en una tabla que ya contiene un registro con la misma clave primaria, podemos usar ON CONFLICT DO para decidir qué acción tomar en ese caso.

INSERT INTO tabla (id, nombre) VALUES (1, 'Juan')
  ON CONFLICT (id) DO UPDATE SET nombre = 'Pedro';

En este caso, si ya existe un registro con el id igual a 1, se actualizará el campo nombre con el valor 'Pedro'.

RETURNING

La función RETURNING en PostgreSQL se utiliza para recuperar valores de una tabla después de haber realizado una operación de inserción, actualización o eliminación.

INSERT INTO tabla (id, nombre) VALUES (1, 'Juan') RETURNING id, nombre;

En este caso, la consulta devolverá los valores 1 y 'Juan', correspondientes a los campos id y nombre de la tabla tabla.

LIKE/ILIKE

Las funciones LIKE e ILIKE son utilizadas para comparar cadenas de texto en PostgreSQL. LIKE es sensible a mayúsculas y minúsculas, mientras que ILIKE no lo es.

SELECT * FROM tabla WHERE nombre LIKE 'J%';

Esta consulta devuelve todos los registros de la tabla tabla donde el campo nombre empieza con la letra “J”.

IS/IS NOT

Las funciones IS y IS NOT se utilizan en PostgreSQL para comparar valores con NULL.

SELECT * FROM tabla WHERE campo IS NULL;

Esta consulta devuelve todos los registros de la tabla tabla donde el campo campo tiene un valor NULL.

aprendimmos ausar las funciones especiales

• ON CONFLICT DO : nos permite insertar valores si esto ya han sido registrado

INSERT INTO estacion(id,nombre,direccion)
VALUES(1,'JHON','av las gaviotas') ON CONFLIC DO (id) DO NOTHING; 

-RETURNING :nos permite retornar los cambios que hicimos a la base de datos ;
-LIKE o ALIKE :nos permite buscar dentro de un texto

Funciones Especiales Principales

PostgreSQL cuenta con una amplia variedad de funciones especiales que permiten realizar una gran cantidad de tareas en la base de datos.

ON CONFLICT DO

El clause “ON CONFLICT DO” en PostgreSQL es una característica de la versión 9.5 o posterior que permite especificar cómo manejar un conflicto de clave única durante una operación de inserción.

Este clause se utiliza en combinación con la sentencia “INSERT” y permite especificar qué acción se debe realizar cuando se intenta insertar una fila que viola una restricción de clave única existente en la tabla.

Hay dos opciones para el clause “ON CONFLICT DO”:

1. “NOTHING”: Esta opción significa que se ignorará la fila que intenta insertarse si hay un conflicto con una restricción de clave única existente.
2. “UPDATE”: Esta opción significa que, en caso de un conflicto de clave única, se actualizarán los valores especificados en la sentencia “UPDATE” en lugar de insertar una nueva fila.

CREATE TABLE pasajero (
  id serial primary key,
  nombre character varying(100),
  direccion_residencia character varying,
  fecha_nacimiento date
);

INSERT INTO pasajero (id, nombre, direccion_residencia, fecha_nacimiento)
VALUES (1, 'Juan', 'Calle 1', '1995-01-01'),
       (2, 'Maria', 'Calle 2', '2000-01-01')
ON CONFLICT (id) DO NOTHING;

En este ejemplo, se crea una tabla “pasajero” con una restricción de clave única en la columna “id”. Luego, se intenta insertar dos filas en la tabla con los valores proporcionados. Si hubiera algún conflicto con una restricción de clave única existente, se ignorará la fila en conflicto y no se insertará.

RETURNING

RETURNING es una cláusula en PostgreSQL que permite devolver los valores de una o más columnas después de haber realizado una operación de inserción, actualización o eliminación.

La cláusula RETURNING permite recuperar el valor de las columnas modificadas y usarlas para realizar tareas adicionales en la misma consulta.

UPDATE table_name SET column1 = value1 WHERE condition RETURNING column2, column3;

En este ejemplo, la consulta actualiza el valor de la columna column1 en la tabla table_name donde la condición se cumple y devuelve los valores de las columnas column2 y column3. Esto puede ser útil para recuperar el valor de una columna autogenerada o para realizar cálculos adicionales después de la operación.

LIKE | ILIKE

LIKE y ILIKE son operadores de comparación en PostgreSQL que se utilizan para comparar una cadena de caracteres con un patrón de búsqueda. La diferencia entre LIKE y ILIKE es que LIKE es sensible a mayúsculas y minúsculas, mientras que ILIKE no lo es.

SELECT * FROM table_name WHERE column_name LIKE 'A%';

En este ejemplo, la consulta seleccionará todas las filas de la tabla table_name donde el valor de la columna column_name comience con la letra “A”.

El símbolo % se utiliza como un comodín para representar cualquier secuencia de caracteres y el símbolo _ se utiliza para representar un solo carácter. Por lo tanto, LIKE 'A%' significa “comienza con A” y LIKE '_A%' significa “tiene dos caracteres antes de A”.

IS / IS NOT

IS y IS NOT son operadores de comparación en PostgreSQL que se utilizan para comparar valores para ver si son iguales o no. Estos operadores son especiales porque permiten comparar valores que podrían ser NULL.

SELECT * FROM table_name WHERE column_name IS NOT NULL;

En este ejemplo, la consulta seleccionará todas las filas de la tabla table_name donde el valor de la columna column_name no sea NULL. La comparación IS NULL se utiliza de la misma manera para comparar valores con NULL.

Es importante tener en cuenta que = y != también pueden utilizarse para comparar valores con NULL, pero estos operadores no se comportan de la misma manera que IS y IS NOT cuando se trata de valores NULL. Por lo tanto, es recomendable utilizar IS y IS NOT cuando se requiera comparar valores con NULL.

OSEA siempre en postgreSQL13, hay que poner " " comillas dobles a los ID, y los ID en mayúscula porque si no no puedes hacer la query

Lo bueno de esta herramienta de postgreSQL, es que puedes descargar tus Querys a CSV, me parece magnifico.

SELECT id, nombre, direccion_residencia, fecha_nacimiento,
case
when fecha_nacimiento > ‘2004-01-01’ then
’MENOR DE EDAD’
ELSE
’ADULTO’
END
from public.pasajero
where nombre ILIKE ‘A%’;;

Yo ni loco lo pongo en rojo, me desespera ese color

SQL es demasiado descriptivo. Literal es como si estuviéramos hablando inglés común y corriente

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.