PostgreSQL

Instalação

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

# macOS
brew install postgresql@16
brew services start postgresql@16

# Docker (recomendado para dev)
docker run -d \
  --name postgres \
  -e POSTGRES_PASSWORD=senha \
  -e POSTGRES_USER=dev \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  postgres:16-alpine

psql — CLI

copypsql -U postgres
psql -U dev -d mydb -h localhost
psql "postgresql://dev:senha@localhost:5432/mydb"

Tipos de Dados

Numéricos

Texto

Data / Hora

Outros

DDL — CREATE / ALTER

copyCREATE TABLE users (
  id          bigserial PRIMARY KEY,
  email       text NOT NULL UNIQUE,
  name        text NOT NULL,
  age         integer CHECK (age >= 0),
  role        text DEFAULT 'user',
  active      boolean DEFAULT true,
  created_at  timestamptz DEFAULT now(),
  updated_at  timestamptz DEFAULT now()
);

CREATE TABLE posts (
  id        bigserial PRIMARY KEY,
  user_id   bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title     text NOT NULL,
  published boolean DEFAULT false,
  tags      text[],
  metadata  jsonb DEFAULT '{}'
);

copy-- ALTER
ALTER TABLE users ADD COLUMN phone text;
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN age SET DEFAULT 18;
ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME TO clientes;

Constraints

copyPRIMARY KEY (id)
UNIQUE (email)
NOT NULL
CHECK (price > 0)
DEFAULT valor
REFERENCES tabela(coluna) ON DELETE CASCADE
REFERENCES tabela(coluna) ON DELETE SET NULL
REFERENCES tabela(coluna) ON DELETE RESTRICT   -- padrão

-- adicionar depois
ALTER TABLE posts ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE posts ADD UNIQUE (slug);
ALTER TABLE posts ADD CONSTRAINT chk_title CHECK (length(title) > 0);

INSERT

copy-- básico
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alfredo');

-- múltiplos
INSERT INTO users (email, name) VALUES
  ('a@b.com', 'Alfredo'),
  ('b@c.com', 'Bruno');

-- RETURNING
INSERT INTO users (email, name) VALUES ('x@y.com', 'Xena')
RETURNING id, created_at;

-- upsert
INSERT INTO users (email, name) VALUES ('a@b.com', 'Atualizado')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- ignorar conflito
INSERT INTO users (email, name) VALUES ('a@b.com', 'Ignorado')
ON CONFLICT DO NOTHING;

SELECT

copySELECT id, email, name FROM users;
SELECT name AS nome FROM users;

-- WHERE
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE role = 'admin' AND active = true;
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE email ILIKE '%gmail%';   -- case-insensitive
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE id IN (1, 2, 3);
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE metadata IS NOT NULL;

-- ORDER / LIMIT / OFFSET
SELECT * FROM users ORDER BY name ASC, created_at DESC;
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;  -- página 3

-- DISTINCT
SELECT DISTINCT role FROM users;

UPDATE / DELETE

copy-- UPDATE
UPDATE users SET name = 'Novo', updated_at = now() WHERE id = 1;
UPDATE users SET active = false WHERE last_login < now() - interval '1 year'
RETURNING id, email;

-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < now() - interval '30 days';
DELETE FROM users RETURNING *;

-- TRUNCATE
TRUNCATE users;
TRUNCATE users CASCADE;

Joins

copy-- LEFT JOIN com COUNT
SELECT u.name, COUNT(p.id) AS total_posts
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

-- join múltiplo
SELECT u.name, p.title, c.body
FROM users u
JOIN posts p ON p.user_id = u.id
JOIN comments c ON c.post_id = p.id
WHERE u.active = true;

Agregações

copySELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT email) FROM users;
SELECT SUM(price), AVG(price), MIN(price), MAX(price) FROM orders;

-- GROUP BY
SELECT role, COUNT(*) AS total FROM users GROUP BY role;

-- HAVING (filtrar após group)
SELECT user_id, COUNT(*) AS total_posts
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY total_posts DESC;

CTEs (Common Table Expressions)

copy-- CTE básico
WITH ativos AS (
  SELECT * FROM users WHERE active = true
)
SELECT * FROM ativos WHERE role = 'admin';

-- múltiplos CTEs
WITH
  top_users AS (
    SELECT user_id, COUNT(*) AS posts FROM posts GROUP BY user_id ORDER BY posts DESC LIMIT 10
  ),
  user_info AS (SELECT id, name, email FROM users)
SELECT u.name, u.email, t.posts
FROM top_users t JOIN user_info u ON u.id = t.user_id;

-- recursivo (hierarquias)
WITH RECURSIVE arvore AS (
  SELECT id, name, parent_id, 0 AS nivel
  FROM categorias WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, a.nivel + 1
  FROM categorias c JOIN arvore a ON a.id = c.parent_id
)
SELECT * FROM arvore ORDER BY nivel, name;

Window Functions

copy-- ROW_NUMBER / RANK
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS posicao,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- PARTITION BY — calcular dentro de grupos
SELECT name, dept, salary,
  AVG(salary) OVER (PARTITION BY dept) AS avg_dept,
  salary - AVG(salary) OVER (PARTITION BY dept) AS diff
FROM employees;

-- LAG / LEAD
SELECT date, revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS diff
FROM sales;

-- NTILE — dividir em quartis
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Subqueries

copy-- no WHERE
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE role = 'premium');

-- correlacionada
SELECT name, (SELECT COUNT(*) FROM posts WHERE user_id = u.id) AS total_posts
FROM users u;

-- EXISTS
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM posts p WHERE p.user_id = u.id);

-- no FROM
SELECT avg_price FROM (
  SELECT AVG(price) AS avg_price FROM products GROUP BY category
) sub
WHERE avg_price > 100;

Índices

copy-- B-tree (padrão)
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user ON posts(user_id);
CREATE INDEX idx_orders_date ON orders(created_at DESC);

-- composto
CREATE INDEX idx_posts_user_pub ON posts(user_id, published);

-- único
CREATE UNIQUE INDEX ON users(email);

-- parcial
CREATE INDEX idx_active ON users(email) WHERE active = true;

-- GIN — arrays, JSONB
CREATE INDEX idx_tags ON posts USING gin(tags);
CREATE INDEX idx_meta ON posts USING gin(metadata);

-- full-text
CREATE INDEX idx_fts ON posts USING gin(to_tsvector('portuguese', title || ' ' || body));

-- ver uso
SELECT indexname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan DESC;

EXPLAIN ANALYZE

copyEXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Transações

copyBEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- rollback
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
ROLLBACK;

-- savepoint
BEGIN;
  INSERT INTO orders (...) VALUES (...);
  SAVEPOINT sp1;
  INSERT INTO order_items (...) VALUES (...);
  ROLLBACK TO sp1;   -- desfaz só desde sp1
  RELEASE SAVEPOINT sp1;
COMMIT;

-- isolamento
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- READ COMMITTED (padrão) | REPEATABLE READ | SERIALIZABLE

JSON / JSONB

copy-- acessar
SELECT metadata->>'cor' FROM produtos;            -- texto
SELECT metadata->'tamanhos' FROM produtos;        -- JSON
SELECT metadata->'tamanhos'->>0 FROM produtos;    -- índice array

-- WHERE
SELECT * FROM produtos WHERE metadata->>'cor' = 'azul';
SELECT * FROM produtos WHERE metadata @> '{"cor": "azul"}';  -- containment

-- UPDATE
UPDATE produtos SET metadata = jsonb_set(metadata, '{cor}', '"vermelho"');
UPDATE produtos SET metadata = metadata || '{"novo": true}';
UPDATE produtos SET metadata = metadata - 'campo';   -- remover chave

-- funções
jsonb_object_keys(metadata)
jsonb_pretty(metadata)

Views

copyCREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE active = true;

-- Materialized view (cache)
CREATE MATERIALIZED VIEW user_stats AS
SELECT u.id, u.name, COUNT(p.id) AS posts
FROM users u LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

REFRESH MATERIALIZED VIEW user_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY user_stats; -- sem lock

Funções e Triggers

copy-- função
CREATE OR REPLACE FUNCTION get_user_posts(uid bigint)
RETURNS TABLE(id bigint, title text) AS $$
BEGIN
  RETURN QUERY
  SELECT p.id, p.title FROM posts p
  WHERE p.user_id = uid AND p.published = true
  ORDER BY p.created_at DESC;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_user_posts(42);

-- trigger updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Roles e Permissões

copyCREATE USER app_user WITH PASSWORD 'senha_forte';
CREATE ROLE readonly;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;
REVOKE ALL ON TABLE users FROM app_user;

-- futuras tabelas
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly;

GRANT readonly TO relatorio_user;

Backup / Restore

copy# backup
pg_dump -U postgres mydb > backup.sql
pg_dump -U postgres -Fc mydb > backup.dump      # comprimido
pg_dump -U postgres -t users mydb > users.sql   # só uma tabela

# restore
psql -U postgres mydb < backup.sql
pg_restore -U postgres -d mydb backup.dump
pg_restore -U postgres -d mydb -j 4 backup.dump # 4 workers

# todas databases + roles
pg_dumpall -U postgres > all.sql

Performance e Manutenção

copy-- vacuum / analyze
VACUUM users;
VACUUM FULL users;      -- compacta (bloqueia tabela)
VACUUM ANALYZE users;

-- queries lentas (requer pg_stat_statements)
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

-- conexões ativas
SELECT pid, state, query_start, query
FROM pg_stat_activity WHERE state != 'idle';

-- tamanho das tabelas
SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::regclass))
FROM pg_tables WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

-- connection string
-- "postgresql://user:password@host:5432/dbname?sslmode=require"