Introduccion a PostgreSQL - Fundamentos de bases de datos relacionales | Tutoriales

Lo que aprenderas en este tutorial

✓ Configuracion de PostgreSQL
✓ Sentencias SQL basicas (SELECT, INSERT, UPDATE, DELETE)
✓ Creacion de tablas y relaciones
✓ Indices y rendimiento
✓ Transacciones
✓ Patrones de consulta practicos

Requisitos previos

Operaciones basicas de linea de comandos
Conocimientos basicos de programacion

Step 1: Configuracion de PostgreSQL

Instalacion

# macOS (Homebrew)
brew install postgresql@16
brew services start postgresql@16

# Ubuntu/Debian
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql

# Windows
# Descargar instalador del sitio oficial de PostgreSQL

Verificar conexion

# Conectar a PostgreSQL
psql -U postgres

# Verificar version
SELECT version();

# Salir
\q

Creacion de base de datos

-- Crear base de datos
CREATE DATABASE tutorial_db;

-- Listar bases de datos
\l

-- Conectar a base de datos
\c tutorial_db

-- Verificar base de datos actual
SELECT current_database();

Step 2: Creacion de tablas y operaciones basicas

Creacion de tablas

-- Tabla de usuarios
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de productos
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de pedidos
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    ordered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabla de detalles de pedido
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL
);

Verificacion de tablas

-- Lista de tablas
\dt

-- Estructura de tabla
\d users
\d+ products

-- Informacion detallada
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'users';

Step 3: Insercion de datos (INSERT)

INSERT basico

-- Insertar una fila
INSERT INTO users (username, email, password_hash, full_name)
VALUES ('tanaka', 'tanaka@example.com', 'hash123', 'Tanaka Taro');

-- Insertar multiples filas
INSERT INTO users (username, email, password_hash, full_name)
VALUES
    ('yamada', 'yamada@example.com', 'hash456', 'Yamada Hanako'),
    ('suzuki', 'suzuki@example.com', 'hash789', 'Suzuki Ichiro'),
    ('sato', 'sato@example.com', 'hash012', 'Sato Misaki');

-- Insertar datos de productos
INSERT INTO products (name, description, price, stock, category)
VALUES
    ('Laptop', 'Laptop de alto rendimiento', 98000, 50, 'electronics'),
    ('Mouse', 'Mouse inalambrico', 3500, 200, 'electronics'),
    ('Teclado', 'Teclado mecanico', 12000, 80, 'electronics'),
    ('Camiseta', '100% algodon', 2500, 150, 'clothing'),
    ('Pantalon vaquero', 'Corte slim', 8000, 60, 'clothing');

Clausula RETURNING

-- Obtener datos insertados
INSERT INTO users (username, email, password_hash)
VALUES ('newuser', 'newuser@example.com', 'newhash')
RETURNING id, username, created_at;

Step 4: Obtencion de datos (SELECT)

SELECT basico

-- Obtener todas las columnas
SELECT * FROM users;

-- Obtener columnas especificas
SELECT id, username, email FROM users;

-- Obtencion condicional
SELECT * FROM users WHERE is_active = true;

-- Multiples condiciones
SELECT * FROM products
WHERE category = 'electronics' AND price < 10000;

-- Condicion OR
SELECT * FROM products
WHERE category = 'electronics' OR category = 'clothing';

-- Clausula IN
SELECT * FROM products
WHERE category IN ('electronics', 'clothing');

-- LIKE (coincidencia parcial)
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Verificacion NULL
SELECT * FROM users WHERE full_name IS NOT NULL;

Ordenamiento y limite

-- Ordenar (ascendente)
SELECT * FROM products ORDER BY price ASC;

-- Ordenar (descendente)
SELECT * FROM products ORDER BY price DESC;

-- Ordenar por multiples columnas
SELECT * FROM products ORDER BY category ASC, price DESC;

-- Limite de filas
SELECT * FROM products LIMIT 10;

-- Con offset (paginacion)
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;  -- 10 filas desde la 21

Funciones de agregacion

-- Conteo
SELECT COUNT(*) FROM users;

-- Suma
SELECT SUM(price * stock) as total_value FROM products;

-- Promedio
SELECT AVG(price) as average_price FROM products;

-- Maximo y minimo
SELECT MAX(price), MIN(price) FROM products;

-- Agrupacion
SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category;

-- HAVING (filtro de grupos)
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) >= 2;

Step 5: Actualizacion y eliminacion de datos

UPDATE

-- Actualizar una fila
UPDATE users
SET full_name = 'Tanaka Taro (actualizado)', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- Actualizar multiples filas
UPDATE products
SET price = price * 0.9  -- 10% de descuento
WHERE category = 'electronics';

-- Clausula RETURNING
UPDATE products
SET stock = stock - 1
WHERE id = 1
RETURNING id, name, stock;

DELETE

-- Eliminacion condicional
DELETE FROM users WHERE id = 5;

-- Eliminar multiples filas
DELETE FROM products WHERE stock = 0;

-- Eliminar todo (precaucion)
-- DELETE FROM products;

-- TRUNCATE para eliminacion rapida
-- TRUNCATE TABLE products;

Step 6: Union de tablas con JOIN

Preparacion de datos de prueba

-- Datos de pedidos
INSERT INTO orders (user_id, total_amount, status)
VALUES
    (1, 101500, 'completed'),
    (2, 15500, 'pending'),
    (1, 8000, 'completed');

-- Datos de detalles de pedido
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (1, 1, 1, 98000),
    (1, 2, 1, 3500),
    (2, 2, 2, 3500),
    (2, 3, 1, 12000),
    (3, 5, 1, 8000);

INNER JOIN

-- Unir usuarios y pedidos
SELECT
    u.username,
    o.id as order_id,
    o.total_amount,
    o.status,
    o.ordered_at
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

LEFT JOIN

-- Todos los usuarios y sus pedidos (incluyendo usuarios sin pedidos)
SELECT
    u.username,
    COUNT(o.id) as order_count,
    COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

Union de multiples tablas

-- Detalle de pedidos (unir usuarios, pedidos y productos)
SELECT
    u.username,
    o.id as order_id,
    p.name as product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) as subtotal
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Step 7: Subconsultas y CTE

Subconsultas

-- Subconsulta en WHERE
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

-- Productos con precio superior al promedio
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subconsulta en FROM
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) as category_stats
WHERE avg_price > 5000;

CTE (Common Table Expression)

-- Consulta legible con clausula WITH
WITH user_orders AS (
    SELECT
        user_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_amount
    FROM orders
    GROUP BY user_id
)
SELECT
    u.username,
    u.email,
    uo.order_count,
    uo.total_amount
FROM users u
INNER JOIN user_orders uo ON u.id = uo.user_id
ORDER BY uo.total_amount DESC;

-- CTE recursivo (datos jerarquicos)
WITH RECURSIVE category_tree AS (
    -- Caso base
    SELECT id, name, parent_id, 0 as level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Caso recursivo
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Step 8: Indices

Creacion de indices

-- Indice de columna unica
CREATE INDEX idx_products_category ON products(category);

-- Indice compuesto
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Indice unico
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Indice parcial
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

Verificacion de indices

-- Lista de indices de tabla
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'products';

-- Verificar plan de ejecucion de consulta
EXPLAIN SELECT * FROM products WHERE category = 'electronics';

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Step 9: Transacciones

Transaccion basica

-- Iniciar transaccion
BEGIN;

-- Reducir stock y crear pedido
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (user_id, total_amount) VALUES (1, 98000);

-- Confirmar si no hay problemas
COMMIT;

-- Revertir si hay problemas
-- ROLLBACK;

Niveles de aislamiento de transaccion

-- Configurar nivel de aislamiento
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Ejecutar consulta
SELECT * FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;

COMMIT;

SAVEPOINT

BEGIN;

INSERT INTO users (username, email, password_hash)
VALUES ('test1', 'test1@example.com', 'hash');

SAVEPOINT sp1;

INSERT INTO users (username, email, password_hash)
VALUES ('test2', 'test2@example.com', 'hash');

-- Revertir solo insercion de test2
ROLLBACK TO sp1;

-- test1 se confirma
COMMIT;

Step 10: Patrones de consulta practicos

Paginacion

-- Basado en offset
SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;  -- Pagina 3

-- Basado en cursor (recomendado)
SELECT * FROM products
WHERE id > 40
ORDER BY id
LIMIT 20;

Busqueda de texto completo

-- Indice de busqueda de texto completo
CREATE INDEX idx_products_search
ON products
USING GIN (to_tsvector('spanish', name || ' ' || COALESCE(description, '')));

-- Consulta de busqueda
SELECT * FROM products
WHERE to_tsvector('spanish', name || ' ' || COALESCE(description, ''))
    @@ to_tsquery('spanish', 'laptop');

Funciones de ventana

-- Ranking
SELECT
    name,
    category,
    price,
    RANK() OVER (ORDER BY price DESC) as overall_rank,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as category_rank
FROM products;

-- Acumulado
SELECT
    ordered_at::date as order_date,
    total_amount,
    SUM(total_amount) OVER (ORDER BY ordered_at) as running_total
FROM orders;

UPSERT (INSERT ON CONFLICT)

-- Actualizar si existe, insertar si no
INSERT INTO products (id, name, price, stock)
VALUES (1, 'Laptop Pro', 120000, 30)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

Mejores practicas

1. Diseno de indices
   - Indices en columnas usadas en WHERE y JOIN
   - Moderacion en tablas con muchas actualizaciones
   - Verificar efectividad con EXPLAIN

2. Optimizacion de consultas
   - Evitar SELECT *
   - Obtener solo columnas necesarias
   - Usar JOIN en lugar de problema N+1

3. Transacciones
   - Mantenerlas cortas
   - Seleccionar nivel de aislamiento apropiado
   - Tener cuidado con deadlocks

4. Seguridad
   - Usar consultas parametrizadas
   - Principio de minimo privilegio
   - Cifrar datos sensibles

Resumen

PostgreSQL es una base de datos relacional poderosa. Dominando operaciones CRUD basicas, JOIN y transacciones, puedes manejar la gestion de datos de muchas aplicaciones. El diseno de indices consciente del rendimiento tambien es importante.

← Volver a la lista