Postgres SQL Snippets: Potencia tu productividad

Si estás utilizando una base de datos Postgres, es muy probable que necesitarás los siguientes fragmentos de SQL para optimizar tus tareas y llevarlas a cabo de manera más eficiente.

Insertar múltiples filas en una sola sentencia SQL

Si necesitas insertar 10 filas de registros a una tabla, podrías optar por ejecutar 10 sentencias SQL, sin embargo, podrías también ejecutar una sola sentencia que inserte las 10 filas de datos, esto permite que solo tengas una conexión abierta a la base de datos, y que el motor de base de datos ejecute una operación (un solo proceso de parseo SQL, bloqueo y asignación de espacio de almacenamiento).

CREATE TABLE blogs (
    id_blog serial PRIMARY KEY,
    name varchar(150) NOT NULL,
    url  varchar(250) NOT NULL,
    created_at timestamptz DEFAULT now()
);

INSERT INTO blogs (name, url) VALUES
('Machine Learning Mastery', 'https://machinelearningmastery.com'),
('El blog Salmón', 'https://www.elblogsalmon.com'),
('Lider Ejecutivo', 'https://liderejecutivo.com'),
('CEO Level', 'https://www.ceolevel.com/blog'),
('Think Big','https://blogthinkbig.com'),
('Solo Data Science', 'https://solodatascience.com'),
('Harvard Business Review', 'https://hbr.org'),
('Bloomberg', 'https://www.bloomberg.com'),
('Business Insider','https://www.businessinsider.com'),
('MIT Technology Review','https://www.technologyreview.es');

Insertar una fila y devolver valor autogenerado.

Los valores auto-generados con DEFAULT/serial/IDENTITY pueden devolverse mediante la instrucción INSERT utilizando la cláusula RETURNING. Desde la perspectiva del código de la aplicación, dicho INSERT se ejecuta como un SELECT que devuelve un conjunto de registros.

INSERT INTO blogs (name, url) VALUES
('The new stack', 'https://thenewstack.io'),
('AWS Compute Blog', 'https://aws.amazon.com/es/blogs/compute/'),
('CiberSeguridad Latam', 'https://www.ciberseguridadlatam.com/')
RETURNING name, id_blog, created_at;

Llaves Primarias UUID autogeneradas

Según el diseño de tu base de datos, se pueden utilizar valores UUID como llave primaria en lugar de valores numéricos.

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 
CREATE TABLE books (
    id    uuid DEFAULT uuid_generate_v4(),
    name  text NOT NULL
);
 
insert into books (name) values
('The Data Science Handbook'),
('Introduction to Statistical Learning'),
('The Art of Data Science')
RETURNING id, name;

Insertar registro si no existe, actualizar en caso contrario

A partir de la versión Postgres 9.5, puedes insertar directamente usando la construcción ON CONFLICT:

-- Creamos tabla de parámetros del negocio
CREATE TABLE business_parameters (
    key   TEXT PRIMARY KEY,
    value TEXT
);

-- Insertamos el parámetro IGV
INSERT INTO business_parameters (key, value) 
     VALUES ('IGV', '17')
ON CONFLICT (key) DO UPDATE SET value=EXCLUDED.value;

-- Insertamos o actualizamos el parámetro IGV
INSERT INTO business_parameters (key, value) 
     VALUES ('IGV', '18')
ON CONFLICT (key) DO UPDATE SET value=EXCLUDED.value;

Copiar filas de una tabla a otra

La declaración INSERT tiene una forma en la que los valores pueden ser proporcionados por una declaración SELECT. Utilice esto para copiar filas de una tabla a otra:

-- Creamos tabla tareas (tasks)
CREATE TABLE tasks (
    id serial PRIMARY KEY,
    name TEXT,
    progress numeric,
    created_at timestamptz DEFAULT now()
);

-- Insertamos registros de prueba
insert into tasks (name, progress) values
('Review architecture diagram', 35),
('Analyze connection issue in billing module', 55),
('Coordinate code review with Joaquín', 100),
('Validate Azure Pipelines Deployment', 75),
('Book a medical appointment with a cardiology specialist', 100);

-- Creamos la tabla pending_tasks donde copiaremos registros
CREATE TABLE pending_tasks (
    id serial PRIMARY KEY,
    name TEXT,
    progress numeric
);

-- Copiar tareas en progreso en tabla pending_tasks
INSERT INTO pending_tasks
SELECT id,name,progress FROM tasks WHERE progress < 100;

También, podemos copiar registros a una nueva tabla, utilizando la declaración CREATE TABLE AS.

-- Crear tabla archived_tasks y copiar tareas completadas
CREATE TABLE archived_tasks AS
SELECT now() AS archival_date, *
FROM tasks WHERE progress = 100;

Eliminar y devolver información eliminada

Puede utilizar la cláusula RETURNING para devolver valores de las filas que se eliminaron mediante una sentencia DELETE.

DELETE FROM books WHERE name like '%Data Science%'
RETURNING name;

Mover filas de una tabla a otra

-- Creamos tabla donde se almacenarán los registros (tareas) eliminados
CREATE TABLE logs_deleted_tasks AS
SELECT * FROM tasks WHERE 1=1;

-- Mover registros (tareas compleadas) a logs_deleted_tasks
WITH completed_tasks AS (
    DELETE FROM tasks
          WHERE progress = 100
      RETURNING *
)
INTO INTO logs_deleted_tasks
SELECT * FROM completed_tasks;

Actualizar filas y devolver valores actualizados

-- Creamos la tabla de jugadores 
create table players (
    id serial primary key,
    name text,
    points numeric default 0,
    status boolean default TRUE
);

-- Insertamos datos de prueba
insert into players (name,points,status) values
('Felipe Escajadillo', 85, true),
('Gonzalo Chacaltana', 55, true),
('Emilia Martinez', 45, false),
('Camila Collins', 95, true);

-- Actualizamos los puntos de los jugadores activos de manera aleatoria
UPDATE players
      SET points = points + (100 * random())::integer
    WHERE status = true
RETURNING id, name, points;

Actualizar algunas filas aleatorias y retonar los valores actualizados

WITH lucky_players AS (
    SELECT id
      FROM players
    WHERE status = true
    ORDER BY random()
     LIMIT 2
)
   UPDATE players
      SET points = points + 100 
    WHERE id IN (SELECT id FROM lucky_players)
RETURNING id,name,points;

Sintaxis CASE, COALESCE, NULLIF en declaraciones SELECT

-- Creamos la tabla libros
CREATE TABLE books (
    id    uuid DEFAULT uuid_generate_v4(),
    name  text NOT NULL,
    type char(1)
);
 
-- Insertamos registros a la tabla libros.
insert into books (name, type) values
('The Da Vinci Code', 'B'),
('The Lord of the Rings', 'B'),
('The Girl with the Dragon Tattoo', 'H'),
('The Fault in Our Stars', 'B'),
('The Hunger Games', 'H');

Uso de la sintaxis: WHEN CASE

-- Utilizamos sintaxis WHEN CASE para evaluar un campo y devolver un valor.
SELECT id,name,CASE WHEN type='B'  THEN 'Best Seller'
            WHEN type='H' THEN 'Hot Sale'
            ELSE 'Other'
       END
FROM books;

Uso de la sintaxis: COALESCE

La declaración COALESCE se utiliza para sustituir un determinado valor en lugar de mostrar NULL

-- insertamos nuevo libro con valor type null
insert into books (name, type) values
('Fifty Shades of Grey', NULL);

1	`SELECT` `name,` `COALESCE(type,` `'')` `FROM` `books;`

Uso de la sintaxis: NULLIF

NULLIF funciona al revés, permitiéndote usar NULL en lugar de un valor determinado:

1	`SELECT` `name,` `NULLIF(type,` `'-')` `FROM` `books;`