Introducao ao PostgreSQL - Fundamentos de Banco de Dados Relacional | Tutoriais

O que voce vai aprender neste tutorial

✓ Configuracao do PostgreSQL
✓ Comandos SQL basicos (SELECT, INSERT, UPDATE, DELETE)
✓ Criacao de tabelas e relacionamentos
✓ Indices e performance
✓ Transacoes
✓ Padroes de consulta praticos

Pre-requisitos

Operacoes basicas de linha de comando
Conhecimento basico de programacao

Step 1: Configuracao do PostgreSQL

Instalacao

# 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
# Baixe o instalador do site oficial do PostgreSQL

Verificacao de conexao

# Conectar ao PostgreSQL
psql -U postgres

# Verificar versao
SELECT version();

# Sair
\q

Criacao de banco de dados

-- Criar banco de dados
CREATE DATABASE tutorial_db;

-- Listar bancos de dados
\l

-- Conectar ao banco de dados
\c tutorial_db

-- Verificar banco de dados atual
SELECT current_database();

Step 2: Criacao de tabelas e operacoes basicas

Criacao de tabelas

-- Tabela 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
);

-- Tabela de produtos
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
);

-- Tabela 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
);

-- Tabela de itens do 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
);

Verificacao de tabelas

-- Listar tabelas
\dt

-- Verificar estrutura da tabela
\d users
\d+ products

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

Step 3: Insercao de dados (INSERT)

INSERT basico

-- Inserir uma linha
INSERT INTO users (username, email, password_hash, full_name)
VALUES ('silva', 'silva@example.com', 'hash123', 'Joao Silva');

-- Inserir multiplas linhas
INSERT INTO users (username, email, password_hash, full_name)
VALUES
    ('santos', 'santos@example.com', 'hash456', 'Maria Santos'),
    ('oliveira', 'oliveira@example.com', 'hash789', 'Carlos Oliveira'),
    ('souza', 'souza@example.com', 'hash012', 'Ana Souza');

-- Inserir dados de produtos
INSERT INTO products (name, description, price, stock, category)
VALUES
    ('Notebook', 'Notebook de alta performance', 4500, 50, 'electronics'),
    ('Mouse', 'Mouse sem fio', 150, 200, 'electronics'),
    ('Teclado', 'Teclado mecanico', 500, 80, 'electronics'),
    ('Camiseta', '100% algodao', 80, 150, 'clothing'),
    ('Calca Jeans', 'Slim fit', 200, 60, 'clothing');

Clausula RETURNING

-- Obter dados inseridos
INSERT INTO users (username, email, password_hash)
VALUES ('newuser', 'newuser@example.com', 'newhash')
RETURNING id, username, created_at;

Step 4: Consulta de dados (SELECT)

SELECT basico

-- Obter todas as colunas
SELECT * FROM users;

-- Obter colunas especificas
SELECT id, username, email FROM users;

-- Obter com condicao
SELECT * FROM users WHERE is_active = true;

-- Multiplas condicoes
SELECT * FROM products
WHERE category = 'electronics' AND price < 500;

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

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

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

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

Ordenacao e limitacao

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

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

-- Ordenar por multiplas colunas
SELECT * FROM products ORDER BY category ASC, price DESC;

-- Limitar quantidade
SELECT * FROM products LIMIT 10;

-- Com offset (paginacao)
SELECT * FROM products
ORDER BY id
LIMIT 10 OFFSET 20;  -- 10 registros a partir do 21o

Funcoes de agregacao

-- Contagem
SELECT COUNT(*) FROM users;

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

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

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

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

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

Step 5: Atualizacao e exclusao de dados

UPDATE

-- Atualizar uma linha
UPDATE users
SET full_name = 'Joao Silva (atualizado)', updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- Atualizar multiplas linhas
UPDATE products
SET price = price * 0.9  -- 10% de desconto
WHERE category = 'electronics';

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

DELETE

-- Exclusao com condicao
DELETE FROM users WHERE id = 5;

-- Excluir multiplas linhas
DELETE FROM products WHERE stock = 0;

-- Excluir todos (cuidado)
-- DELETE FROM products;

-- TRUNCATE para exclusao rapida
-- TRUNCATE TABLE products;

Step 6: JOIN para uniao de tabelas

Preparacao de dados de teste

-- Dados de pedidos
INSERT INTO orders (user_id, total_amount, status)
VALUES
    (1, 4650, 'completed'),
    (2, 650, 'pending'),
    (1, 200, 'completed');

-- Dados de itens de pedido
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (1, 1, 1, 4500),
    (1, 2, 1, 150),
    (2, 2, 2, 150),
    (2, 3, 1, 500),
    (3, 5, 1, 200);

INNER JOIN

-- Unir usuarios e 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 os usuarios e pedidos (incluindo usuarios sem 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;

Uniao de multiplas tabelas

-- Detalhes do pedido (unir usuarios, pedidos e produtos)
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: Subqueries e CTE

Subqueries

-- Subquery no WHERE
SELECT * FROM users
WHERE id IN (
    SELECT DISTINCT user_id FROM orders
);

-- Produtos com preco acima da media
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery no FROM
SELECT category, avg_price
FROM (
    SELECT category, AVG(price) as avg_price
    FROM products
    GROUP BY category
) as category_stats
WHERE avg_price > 300;

CTE (Common Table Expression)

-- Consulta mais legivel com 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 recursiva (dados hierarquicos)
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

Criacao de indices

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

-- Indice composto
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;

Verificacao de indices

-- Listar indices da tabela
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'products';

-- Verificar plano de execucao da consulta
EXPLAIN SELECT * FROM products WHERE category = 'electronics';

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;

Step 9: Transacoes

Transacao basica

-- Iniciar transacao
BEGIN;

-- Reduzir estoque e criar pedido
UPDATE products SET stock = stock - 1 WHERE id = 1;
INSERT INTO orders (user_id, total_amount) VALUES (1, 4500);

-- Se nao houver problemas, commit
COMMIT;

-- Se houver problemas, rollback
-- ROLLBACK;

Niveis de isolamento de transacao

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

-- Executar consultas
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');

-- Desfazer apenas insercao do test2
ROLLBACK TO sp1;

-- test1 sera commitado
COMMIT;

Step 10: Padroes de consulta praticos

Paginacao

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

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

Busca de texto completo

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

-- Consulta de busca
SELECT * FROM products
WHERE to_tsvector('portuguese', name || ' ' || COALESCE(description, ''))
    @@ to_tsquery('portuguese', 'notebook');

Funcoes de janela

-- 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)

-- Se existir atualiza, se nao existir insere
INSERT INTO products (id, name, price, stock)
VALUES (1, 'Notebook Pro', 5500, 30)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

Boas praticas

1. Design de indices
   - Criar indices em colunas usadas em WHERE e JOIN
   - Usar com moderacao em tabelas com muitas atualizacoes
   - Verificar eficacia com EXPLAIN

2. Otimizacao de consultas
   - Evitar SELECT *
   - Buscar apenas colunas necessarias
   - Usar JOIN ao inves de N+1 queries

3. Transacoes
   - Manter curtas
   - Escolher nivel de isolamento apropriado
   - Cuidado com deadlocks

4. Seguranca
   - Usar consultas parametrizadas
   - Principio do menor privilegio
   - Criptografar dados sensiveis

Resumo

PostgreSQL e um banco de dados relacional poderoso. Dominando operacoes CRUD basicas, JOIN e transacoes, voce pode gerenciar dados de muitas aplicacoes. Design de indices consciente de performance tambem e importante.

← Voltar para a lista