En esta página

INSERT, UPDATE y DELETE

12 min lectura TextoCap. 2 — Consultas básicas

INSERT, UPDATE y DELETE

En la lección anterior aprendiste a leer datos con SELECT. Ahora es turno del DML (Data Manipulation Language): las instrucciones que crean, modifican y eliminan datos. Estas operaciones requieren más cuidado que las consultas, porque sus efectos son permanentes una vez que se confirman.

INSERT INTO — Insertar datos

La instrucción INSERT INTO añade nuevas filas a una tabla.

Inserción de una fila

-- Sintaxis básica con columnas explícitas (SIEMPRE recomendada)
INSERT INTO categorias (nombre)
VALUES ('Electrónica');

-- Inserción con todos los campos, incluyendo los opcionales
INSERT INTO productos (nombre, descripcion, precio, stock, categoria_id, activo)
VALUES ('Laptop Pro 15"', 'Laptop con chip M3 Pro, 18GB RAM', 1299.99, 5, 1, true);

-- Si omites columnas con DEFAULT, se usa el valor por defecto
INSERT INTO productos (nombre, precio, stock, categoria_id)
VALUES ('Teclado Compacto', 69.99, 25, 1);
-- activo = true (DEFAULT), creado_en = NOW() (DEFAULT)

[!warning] Nunca uses INSERT sin especificar las columnas en código de producción. Si omites la lista de columnas, el INSERT depende del orden de columnas de la tabla. Cuando alguien modifique la tabla (añadir, reordenar columnas), tu código se romperá silenciosamente con datos incorrectos.

Inserción de múltiples filas

Insertar múltiples filas en una sola instrucción es significativamente más eficiente que múltiples INSERT individuales:

-- Inserción masiva en una sola instrucción
INSERT INTO categorias (nombre) VALUES
  ('Ropa'),
  ('Libros'),
  ('Hogar'),
  ('Deportes'),
  ('Jardín'),
  ('Mascotas');

-- También funciona con columnas más complejas
INSERT INTO productos (nombre, precio, stock, categoria_id) VALUES
  ('Auriculares BT',        199.99, 15, 1),
  ('Mouse Inalámbrico',      49.99, 45, 1),
  ('Teclado Mecánico',       89.99, 30, 1),
  ('Camiseta Algodón',       19.99, 200, 2),
  ('Jeans Slim',             59.99, 80, 2),
  ('Clean Code',             35.99, 50, 3),
  ('The Pragmatic Programmer',42.99, 40, 3),
  ('Sartén 28cm',            34.99, 25, 4),
  ('Pelota de Fútbol',       24.99, 60, 5),
  ('Zapatillas Running',    119.99, 35, 5);

INSERT con SELECT

Puedes insertar filas basándote en el resultado de una consulta:

-- Copiar productos activos a una tabla de archivo
CREATE TABLE productos_backup AS SELECT * FROM productos WHERE false; -- Crea tabla vacía con misma estructura

INSERT INTO productos_backup
SELECT * FROM productos WHERE activo = true;

-- Crear un catálogo de precios a partir de los productos
CREATE TABLE historial_precios (
  producto_id INTEGER NOT NULL REFERENCES productos(id),
  precio      NUMERIC(10,2) NOT NULL,
  registrado_en TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO historial_precios (producto_id, precio)
SELECT id, precio FROM productos;

La cláusula RETURNING

RETURNING es una extensión de PostgreSQL (también en SQLite, no en MySQL estándar) que devuelve datos de las filas afectadas por un INSERT, UPDATE o DELETE. Es tremendamente útil para obtener valores generados automáticamente:

-- Obtener el ID generado después de un INSERT
INSERT INTO categorias (nombre)
VALUES ('Tecnología')
RETURNING id, nombre;
-- Resultado: id=7, nombre='Tecnología'

-- RETURNING * devuelve todas las columnas
INSERT INTO productos (nombre, precio, stock, categoria_id)
VALUES ('Smartwatch Pro', 299.99, 20, 1)
RETURNING *;
-- Devuelve la fila completa, incluyendo id, activo=true y creado_en=<timestamp actual>

-- RETURNING con alias
INSERT INTO usuarios (email, nombre)
VALUES ('[email protected]', 'Carlos')
RETURNING id AS usuario_id, creado_en AS fecha_registro;

Esto es especialmente valioso en aplicaciones: evita tener que hacer un SELECT adicional para obtener el ID del registro recién creado.

UPDATE — Actualizar datos

UPDATE modifica los valores de columnas existentes en filas que cumplen una condición.

-- Actualizar una columna de una fila específica
UPDATE productos
SET precio = 1199.99
WHERE id = 1;

-- Actualizar múltiples columnas a la vez
UPDATE productos
SET
  precio = 1149.99,
  descripcion = 'Laptop con chip M3 Pro, 18GB RAM, actualizado',
  activo = true
WHERE id = 1;

-- Actualizar basándose en el valor actual (incremento)
UPDATE productos
SET stock = stock + 50
WHERE id = 3;

-- Actualizar múltiples filas con la misma condición
UPDATE productos
SET activo = false
WHERE stock = 0;

-- Actualizar con expresión calculada
UPDATE productos
SET precio = ROUND(precio * 1.10, 2)   -- Subir precios 10%
WHERE categoria_id = 1;                -- Solo electrónica

UPDATE con RETURNING

-- Actualizar y obtener los nuevos valores en una sola operación
UPDATE productos
SET
  precio = precio * 0.90,  -- 10% de descuento
  descripcion = descripcion || ' [EN OFERTA]'
WHERE categoria_id = 2
RETURNING id, nombre, precio AS nuevo_precio;

UPDATE con FROM (unir tablas en UPDATE)

-- Actualizar precios basándose en una tabla de ajustes
CREATE TEMP TABLE ajustes_precio (
  producto_id INTEGER,
  nuevo_precio NUMERIC(10,2)
);

INSERT INTO ajustes_precio VALUES (1, 999.99), (2, 149.99), (11, 39.99);

-- Actualizar usando otra tabla como fuente
UPDATE productos p
SET precio = a.nuevo_precio
FROM ajustes_precio a
WHERE p.id = a.producto_id
RETURNING p.id, p.nombre, p.precio AS precio_actualizado;

DELETE — Eliminar datos

DELETE FROM elimina filas de una tabla.

-- Eliminar una fila específica
DELETE FROM productos WHERE id = 99;

-- Eliminar múltiples filas
DELETE FROM productos WHERE stock = 0 AND activo = false;

-- Eliminar con condición compuesta
DELETE FROM historial_precios
WHERE registrado_en < NOW() - INTERVAL '1 year';

-- DELETE con RETURNING (saber qué se eliminó)
DELETE FROM productos
WHERE categoria_id = 6
RETURNING id, nombre;

-- DELETE con subquery
DELETE FROM pedidos_items
WHERE pedido_id IN (
  SELECT id FROM pedidos WHERE estado = 'cancelado' AND creado_en < NOW() - INTERVAL '90 days'
);

[!warning] El WHERE en UPDATE y DELETE es CRÍTICO. Un UPDATE productos SET precio = 0; sin WHERE actualiza TODOS los productos. Un DELETE FROM pedidos; sin WHERE borra TODOS los pedidos. Siempre verifica tu condición con un SELECT antes de ejecutar un UPDATE o DELETE masivo: SELECT * FROM productos; (para ver qué afectaría) antes de UPDATE productos SET precio = 0;. En psql, puedes usar BEGIN; y ROLLBACK; para probar de forma segura.

TRUNCATE — Vaciar una tabla

TRUNCATE es más rápido que DELETE sin condición para vaciar una tabla completa, porque no registra cada fila eliminada en el WAL:

-- Vaciar una tabla completamente (más rápido que DELETE sin WHERE)
TRUNCATE TABLE historial_precios;

-- Vaciar y reiniciar las secuencias (IDs seriales vuelven a 1)
TRUNCATE TABLE productos RESTART IDENTITY;

-- Vaciar múltiples tablas a la vez
TRUNCATE TABLE pedidos_items, pedidos;

-- CASCADE: también trunca las tablas que referencian con FK
TRUNCATE TABLE categorias CASCADE;  -- También trunca productos (que referencia categorias)

La diferencia principal con DELETE:

  • TRUNCATE no dispara triggers ON DELETE
  • TRUNCATE no puede usarse con WHERE
  • TRUNCATE no devuelve el número de filas eliminadas
  • TRUNCATE es transaccional en PostgreSQL (se puede hacer ROLLBACK)

UPSERT con ON CONFLICT

El UPSERT (Update or Insert) es una de las características más útiles de PostgreSQL. Permite insertar un registro y, si ya existe (violando una restricción única), actualizarlo en lugar de fallar:

-- ON CONFLICT DO NOTHING: ignorar el conflicto silenciosamente
INSERT INTO categorias (nombre)
VALUES ('Electrónica')
ON CONFLICT (nombre) DO NOTHING;

-- ON CONFLICT DO UPDATE: actualizar cuando hay conflicto (verdadero UPSERT)
INSERT INTO productos (sku, nombre, precio, stock, categoria_id)
VALUES ('LAP-001', 'Laptop Pro 15" 2025', 1199.99, 10, 1)
ON CONFLICT (sku) DO UPDATE
  SET
    nombre    = EXCLUDED.nombre,
    precio    = EXCLUDED.precio,
    stock     = EXCLUDED.stock
RETURNING id, nombre, precio;

-- EXCLUDED hace referencia a los valores que se INTENTABAN insertar
-- (los que causaron el conflicto)

-- UPSERT solo si el nuevo precio es menor (lógica condicional)
INSERT INTO productos (sku, nombre, precio, stock, categoria_id)
VALUES ('MOU-001', 'Mouse Pro', 39.99, 50, 1)
ON CONFLICT (sku) DO UPDATE
  SET precio = LEAST(EXCLUDED.precio, productos.precio)
WHERE EXCLUDED.precio < productos.precio;

-- UPSERT con ON CONFLICT en clave primaria
INSERT INTO historial_precios (producto_id, precio)
VALUES (1, 999.99)
ON CONFLICT (producto_id) DO UPDATE
  SET
    precio = EXCLUDED.precio,
    registrado_en = NOW();
-- Sincronizar productos desde una fuente externa
-- Si el SKU ya existe → actualizar precio y stock
-- Si no existe → insertar nuevo producto

WITH productos_importados (sku, nombre, precio, stock, categoria_id) AS (
  VALUES
    ('LAP-001', 'Laptop Pro 15"',   1099.99, 8, 1),
    ('AUR-002', 'Auriculares Pro',   179.99, 20, 1),
    ('CAM-003', 'Camiseta Premium',   24.99, 150, 2)
)
INSERT INTO productos (sku, nombre, precio, stock, categoria_id)
SELECT sku, nombre, precio, stock, categoria_id
FROM productos_importados
ON CONFLICT (sku) DO UPDATE
  SET
    nombre    = EXCLUDED.nombre,
    precio    = EXCLUDED.precio,
    stock     = EXCLUDED.stock
RETURNING id, sku, nombre,
          CASE WHEN xmax = 0 THEN 'insertado' ELSE 'actualizado' END AS operacion;
-- xmax = 0 indica que la fila fue insertada (no tenía versión anterior)

[!tip] El truco xmax = 0: en el RETURNING de un UPSERT, puedes determinar si cada fila fue insertada o actualizada verificando el sistema de columnas xmax. Si xmax = 0, la fila fue insertada; si xmax != 0, fue actualizada. Esto es un detalle de implementación interna de PostgreSQL, pero es muy útil para logging y auditoría.

Buenas prácticas de escritura de datos

  1. Siempre usa transacciones para operaciones que afectan múltiples tablas: si insertas un pedido y sus líneas, ambas operaciones deben ser atómicas.

  2. Usa RETURNING para evitar consultas adicionales: en lugar de INSERT + SELECT, usa INSERT ... RETURNING.

  3. Prueba UPDATE y DELETE con SELECT primero: reemplaza temporalmente UPDATE/DELETE con SELECT usando la misma condición WHERE para ver qué filas afectarías.

  4. Usa ON CONFLICT para idempotencia: los sistemas distribuidos necesitan que las operaciones sean seguras de reintentar. ON CONFLICT hace que tus inserciones sean idempotentes.

  5. Considera soft deletes para datos importantes: en lugar de DELETE FROM pedidos WHERE id = 5, considera UPDATE pedidos SET eliminado_en = NOW() WHERE id = 5. Así puedes recuperar datos borrados por error.

-- Patrón de soft delete
ALTER TABLE pedidos ADD COLUMN eliminado_en TIMESTAMPTZ;

-- "Eliminar"
UPDATE pedidos SET eliminado_en = NOW() WHERE id = 5;

-- Consultar solo activos
SELECT * FROM pedidos WHERE eliminado_en IS NULL;

-- Recuperar un pedido "eliminado"
UPDATE pedidos SET eliminado_en = NULL WHERE id = 5;

Con estas herramientas puedes gestionar el ciclo de vida completo de los datos. En la siguiente lección exploraremos las funciones de agregación: cómo responder preguntas como "¿cuántos productos hay?", "¿cuál es el producto más caro?", "¿cuánto vende cada categoría?".

nextSteps:

  • slug: funciones-de-agregacion label: "Siguiente: Funciones de agregación"