PostgreSQL Avanzado

PostgreSQL tiene características únicas que lo hacen extremadamente potente: JSONB para documentos semi-estructurados, arrays nativos, funciones de generación de datos, extensiones como uuid-ossp, y búsqueda full-text integrada.

-- JSONB: almacenar y consultar JSON eficientemente
CREATE TABLE productos (
  id       SERIAL PRIMARY KEY,
  nombre   VARCHAR(200),
  metadata JSONB  -- atributos variables por categoría
);

INSERT INTO productos (nombre, metadata) VALUES
  ('Laptop Pro', '{"ram": 16, "cpu": "M3", "color": "silver", "tags": ["nuevo","oferta"]}'),
  ('Mouse X',   '{"dpi": 4000, "inalambrico": true, "color": "negro"}');

-- Operadores JSONB
-- ->  : accede a la clave, devuelve JSONB
-- ->> : accede a la clave, devuelve TEXT
-- @>  : contiene (el JSONB izquierdo contiene al derecho)
SELECT nombre, metadata->'ram' AS ram_json     -- JSONB: 16
FROM productos WHERE metadata->>'cpu' = 'M3';

SELECT nombre FROM productos
WHERE metadata @> '{"inalambrico": true}';

SELECT nombre FROM productos
WHERE metadata->>'color' = 'negro';

-- Índice GIN para JSONB (acelera @> y búsquedas de claves)
CREATE INDEX idx_productos_metadata ON productos USING GIN (metadata);

-- ARRAYS nativos
CREATE TABLE articulos (
  id   SERIAL PRIMARY KEY,
  titulo VARCHAR(200),
  tags TEXT[]
);
INSERT INTO articulos (titulo, tags)
VALUES ('Intro a SQL', ARRAY['sql', 'bases-de-datos', 'tutorial']);

SELECT titulo FROM articulos WHERE 'sql' = ANY(tags);

-- GENERATE_SERIES: generar secuencias de datos
SELECT generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 month'::INTERVAL);

-- Extensión uuid-ossp: generar UUIDs
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();  -- ej: '550e8400-e29b-41d4-a716-446655440000'

-- Búsqueda Full-Text
SELECT titulo FROM articulos
WHERE to_tsvector('spanish', titulo) @@ to_tsquery('spanish', 'bases & datos');

-- Índice para búsqueda full-text
CREATE INDEX idx_articulos_fts ON articulos
USING GIN (to_tsvector('spanish', titulo));