⚡ v16+ compatível
PostgreSQL
Referência Completa
Todos os comandos essenciais — DDL, DML, DQL, DCL, TCL, Triggers, Functions, Indexes, JSON, Window Functions e administração do banco.
SELECT — Consultas (DQL)
Data Query Language
SELECT básico
Recupera linhas de uma ou mais tabelas com filtros, ordenação e limite
SELECT coluna1, coluna2, coluna3
FROM tabela
WHERE coluna1 = 'valor'
AND coluna2 > 100
ORDER BY coluna1 ASC, coluna2 DESC
LIMIT 10 OFFSET 20;
SELECT DISTINCT / COUNT / GROUP BY / HAVING
Elimina duplicatas e agrega resultados com filtro sobre grupos
SELECT DISTINCT categoria,
COUNT(*) AS total,
SUM(valor) AS soma,
AVG(valor) AS media,
MAX(valor) AS maximo,
MIN(valor) AS minimo
FROM pedidos
GROUP BY categoria
HAVING COUNT(*) > 5
ORDER BY total DESC;
Operadores de filtro
LIKE, ILIKE, IN, BETWEEN, IS NULL, SIMILAR TO, regexp
SELECT * FROM clientes
WHERE nome ILIKE '%silva%' -- insensível a maiúsculas
AND status IN ('ativo', 'pendente')
AND idade BETWEEN 18 AND 65
AND email IS NOT NULL
AND cpf ~ '^[0-9]{11}$'; -- regex posix
CASE WHEN
Expressão condicional inline para transformar valores
SELECT nome,
CASE
WHEN salario > 10000 THEN 'Senior'
WHEN salario > 5000 THEN 'Pleno'
ELSE 'Junior'
END AS nivel
FROM funcionarios;
JOINs — Relacionamentos
Combinação de tabelas
INNER JOIN
Retorna apenas linhas com correspondência nos dois lados
SELECT p.nome, c.razao_social, p.valor
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id
INNER JOIN produtos pr ON p.produto_id = pr.id;
LEFT / RIGHT / FULL OUTER JOIN
Mantém todos os registros de um ou ambos os lados mesmo sem correspondência
SELECT c.nome, p.valor
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id;
-- RIGHT JOIN: todos de pedidos, nulos de clientes
-- FULL OUTER JOIN: tudo de ambos, nulos onde não há par
-- CROSS JOIN: produto cartesiano (sem ON)
SELF JOIN / LATERAL JOIN
Join da tabela com ela mesma; LATERAL referencia colunas anteriores
-- SELF JOIN: hierarquia
SELECT e.nome, g.nome AS gestor
FROM funcionarios e
LEFT JOIN funcionarios g ON e.gestor_id = g.id;
-- LATERAL JOIN
SELECT u.nome, ult.criado_em
FROM usuarios u
JOIN LATERAL (
SELECT criado_em FROM logs
WHERE usuario_id = u.id
ORDER BY criado_em DESC LIMIT 1
) ult ON TRUE;
Subqueries
Consultas aninhadas
Subquery no WHERE / IN / EXISTS
Filtra com base em resultado de outra consulta
-- IN
SELECT * FROM produtos
WHERE id IN (SELECT produto_id FROM pedidos WHERE valor > 500);
-- EXISTS (mais eficiente que IN para grandes volumes)
SELECT * FROM clientes c
WHERE EXISTS (
SELECT 1 FROM pedidos p
WHERE p.cliente_id = c.id
);
-- Subquery como coluna calculada
SELECT nome,
(SELECT COUNT(*) FROM pedidos WHERE cliente_id = c.id) AS total_pedidos
FROM clientes c;
CTEs — WITH / Recursivo
Common Table Expressions
WITH (CTE)
Define consultas nomeadas temporárias reutilizáveis na mesma query
WITH
vendas_mes AS (
SELECT vendedor_id, SUM(valor) AS total
FROM pedidos
WHERE criado_em >= date_trunc('month', now())
GROUP BY vendedor_id
),
meta AS (
SELECT id, meta_valor FROM metas
)
SELECT v.vendedor_id, vm.total, m.meta_valor,
vm.total - m.meta_valor AS diferenca
FROM vendedores v
LEFT JOIN vendas_mes vm ON v.id = vm.vendedor_id
LEFT JOIN meta m ON v.id = m.id;
WITH RECURSIVE
Percorre hierarquias e grafos com consulta auto-referenciada
WITH RECURSIVE hierarquia AS (
-- Base: raiz
SELECT id, nome, pai_id, 0 AS nivel
FROM categorias
WHERE pai_id IS NULL
UNION ALL
-- Recursivo: filhos
SELECT c.id, c.nome, c.pai_id, h.nivel + 1
FROM categorias c
INNER JOIN hierarquia h ON c.pai_id = h.id
)
SELECT repeat(' ', nivel) || nome AS arvore
FROM hierarquia
ORDER BY nivel, nome;
Window Functions
Funções de janela / analíticas
ROW_NUMBER / RANK / DENSE_RANK / NTILE
Numera e classifica linhas dentro de partições sem colapsar o resultado
SELECT
nome, departamento, salario,
ROW_NUMBER() OVER (PARTITION BY departamento ORDER BY salario DESC) AS posicao,
RANK() OVER (PARTITION BY departamento ORDER BY salario DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY departamento ORDER BY salario DESC) AS dense_rank,
NTILE(4) OVER (ORDER BY salario) AS quartil
FROM funcionarios;
LAG / LEAD / SUM acumulado / FIRST_VALUE
Acessa linhas anteriores/posteriores e calcula acumulados dentro da janela
SELECT
data, valor,
LAG(valor, 1) OVER (ORDER BY data) AS valor_anterior,
LEAD(valor, 1) OVER (ORDER BY data) AS valor_proximo,
SUM(valor) OVER (ORDER BY data) AS acumulado,
AVG(valor) OVER (ORDER BY data
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS media_movel,
FIRST_VALUE(valor) OVER (PARTITION BY mes ORDER BY data) AS primeiro_do_mes
FROM vendas;
INSERT / UPDATE / DELETE (DML)
Data Manipulation Language
INSERT
Insere uma ou múltiplas linhas; RETURNING retorna dados inseridos
-- Simples
INSERT INTO clientes (nome, email, criado_em)
VALUES ('Ana Silva', 'ana@email.com', now());
-- Múltiplas linhas
INSERT INTO produtos (nome, preco) VALUES
('Teclado', 150.00),
('Mouse', 80.00),
('Monitor', 900.00);
-- INSERT a partir de SELECT
INSERT INTO clientes_vip (cliente_id, desde)
SELECT id, now() FROM clientes WHERE total_compras > 10000;
-- Retornar o que foi inserido
INSERT INTO pedidos (produto_id, valor)
VALUES (1, 250.00)
RETURNING id, criado_em;
UPSERT — INSERT ON CONFLICT
Insere ou atualiza se houver conflito de chave (merge)
INSERT INTO configuracoes (chave, valor)
VALUES ('tema', 'escuro')
ON CONFLICT (chave) DO UPDATE
SET valor = EXCLUDED.valor,
atualizado_em = now();
-- Ignorar se já existe
INSERT INTO tags (nome) VALUES ('postgres')
ON CONFLICT DO NOTHING;
UPDATE
Atualiza linhas existentes; FROM permite JOIN no UPDATE
-- Simples
UPDATE produtos
SET preco = preco * 1.10,
atualizado_em = now()
WHERE categoria_id = 3
RETURNING id, preco;
-- UPDATE com JOIN via FROM
UPDATE pedidos p
SET status = 'cancelado'
FROM clientes c
WHERE p.cliente_id = c.id
AND c.inadimplente = TRUE;
DELETE / TRUNCATE
Remove linhas; TRUNCATE apaga tudo mais rápido (não loggável por trigger)
DELETE FROM logs
WHERE criado_em < now() - INTERVAL '90 days'
RETURNING id;
-- TRUNCATE: zera a tabela, reinicia SERIAL
TRUNCATE TABLE sessoes RESTART IDENTITY CASCADE;
CREATE / ALTER / DROP (DDL)
Data Definition Language
CREATE TABLE
Cria tabela com tipos, constraints, chaves e defaults
CREATE TABLE clientes (
id BIGSERIAL PRIMARY KEY,
nome VARCHAR(120) NOT NULL,
email TEXT NOT NULL UNIQUE,
cpf CHAR(11) UNIQUE,
ativo BOOLEAN NOT NULL DEFAULT TRUE,
criado_em TIMESTAMPTZ NOT NULL DEFAULT now(),
saldo NUMERIC(15,2) DEFAULT 0 CHECK (saldo >= 0),
CONSTRAINT fk_cidade
FOREIGN KEY (cidade_id)
REFERENCES cidades(id)
ON DELETE SET NULL
);
ALTER TABLE
Modifica estrutura: colunas, constraints, renomear, tipo
ALTER TABLE clientes
ADD COLUMN telefone TEXT,
ADD COLUMN cidade_id BIGINT,
DROP COLUMN campo_antigo,
ALTER COLUMN nome SET NOT NULL,
ALTER COLUMN saldo TYPE NUMERIC(18,4),
RENAME COLUMN telefone TO celular,
ADD CONSTRAINT chk_saldo CHECK (saldo >= 0),
DROP CONSTRAINT chk_antigo;
ALTER TABLE clientes RENAME TO usuarios;
DROP / CREATE SCHEMA / SEQUENCE
Remove objetos; gerencia schemas e sequências numéricas
DROP TABLE IF EXISTS temp_importacao CASCADE;
DROP VIEW IF EXISTS vw_relatorio;
DROP INDEX IF EXISTS idx_email;
DROP FUNCTION IF EXISTS calcular_imposto(numeric);
-- Schema
CREATE SCHEMA IF NOT EXISTS relatorios;
SET search_path TO relatorios, public;
-- Sequence manual
CREATE SEQUENCE seq_protocolo START 1000 INCREMENT 1;
SELECT nextval('seq_protocolo'); -- próximo valor
SELECT currval('seq_protocolo'); -- valor atual
Transações (TCL)
Transaction Control Language
⚠️ Sem BEGIN explícito, cada comando é auto-commitado. Use transações para garantir atomicidade (ACID).
BEGIN / COMMIT / ROLLBACK / SAVEPOINT
Controla o ciclo de vida de uma transação com pontos de salvamento
BEGIN; -- inicia transação
UPDATE contas SET saldo = saldo - 500 WHERE id = 1;
SAVEPOINT sp1; -- ponto de salvamento
UPDATE contas SET saldo = saldo + 500 WHERE id = 2;
-- Se algo der errado parcialmente:
ROLLBACK TO SAVEPOINT sp1;
-- Ou cancela tudo:
ROLLBACK;
COMMIT; -- confirma tudo
-- Nível de isolamento
BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN ISOLATION LEVEL REPEATABLE READ;
Triggers
Gatilhos automáticos
CREATE TRIGGER — estrutura completa
Executa uma função automaticamente antes ou após eventos DML
-- 1. Criar a função do trigger (retorna TRIGGER)
CREATE OR REPLACE FUNCTION fn_auditoria()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO auditoria (tabela, operacao, dado_antigo, usuario, momento)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), current_user, now());
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO auditoria (tabela, operacao, dado_novo, usuario, momento)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(NEW), current_user, now());
RETURN NEW;
ELSE -- UPDATE
INSERT INTO auditoria (tabela, operacao, dado_antigo, dado_novo, usuario, momento)
VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), current_user, now());
RETURN NEW;
END IF;
END;
$$;
-- 2. Associar o trigger à tabela
CREATE TRIGGER tg_auditoria_clientes
AFTER INSERT OR UPDATE OR DELETE
ON clientes
FOR EACH ROW
EXECUTE FUNCTION fn_auditoria();
Trigger BEFORE — validação / modificação
Intercepta o dado antes de persistir; permite modificar ou bloquear
CREATE OR REPLACE FUNCTION fn_normalizar_email()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
NEW.email := lower(trim(NEW.email));
NEW.nome := initcap(trim(NEW.nome));
IF NEW.email NOT LIKE '%@%' THEN
RAISE EXCEPTION 'Email inválido: %', NEW.email;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER tg_normalizar
BEFORE INSERT OR UPDATE ON clientes
FOR EACH ROW EXECUTE FUNCTION fn_normalizar_email();
Variáveis especiais de Trigger
Variáveis disponíveis automaticamente dentro de funções de trigger
NEW -- nova linha (INSERT / UPDATE)
OLD -- linha anterior (UPDATE / DELETE)
TG_OP -- 'INSERT' | 'UPDATE' | 'DELETE' | 'TRUNCATE'
TG_TABLE_NAME -- nome da tabela
TG_WHEN -- 'BEFORE' | 'AFTER' | 'INSTEAD OF'
TG_NARGS -- número de argumentos
TG_ARGV[] -- array de argumentos do trigger
TG_LEVEL -- 'ROW' | 'STATEMENT'
Gerenciar Triggers
Listar, desabilitar, habilitar e remover triggers
-- Listar triggers
SELECT trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers
ORDER BY event_object_table;
ALTER TABLE clientes DISABLE TRIGGER tg_auditoria_clientes;
ALTER TABLE clientes ENABLE TRIGGER tg_auditoria_clientes;
ALTER TABLE clientes DISABLE TRIGGER ALL;
DROP TRIGGER IF EXISTS tg_auditoria_clientes ON clientes;
Functions & Procedures (PL/pgSQL)
Lógica procedural no banco
CREATE FUNCTION
Função que retorna um valor; pode ser usada em SELECT
CREATE OR REPLACE FUNCTION calcular_desconto(
p_valor NUMERIC,
p_percentual NUMERIC DEFAULT 10
)
RETURNS NUMERIC
LANGUAGE plpgsql
STABLE -- IMMUTABLE | STABLE | VOLATILE
AS $$
DECLARE
v_desconto NUMERIC;
BEGIN
v_desconto := p_valor * (p_percentual / 100);
RETURN p_valor - v_desconto;
END;
$$;
-- Uso
SELECT calcular_desconto(500.00, 15);
SELECT calcular_desconto(200.00); -- usa default 10%
FUNCTION que retorna tabela (SETOF / TABLE)
Retorna múltiplas linhas como se fosse uma tabela
CREATE OR REPLACE FUNCTION buscar_clientes_ativos(p_limite INT DEFAULT 100)
RETURNS TABLE(id BIGINT, nome TEXT, email TEXT)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
SELECT c.id, c.nome, c.email
FROM clientes c
WHERE c.ativo = TRUE
LIMIT p_limite;
END;
$$;
-- Uso
SELECT * FROM buscar_clientes_ativos(50);
CREATE PROCEDURE
Procedure não retorna valor; pode conter COMMIT/ROLLBACK internos
CREATE OR REPLACE PROCEDURE transferir_saldo(
p_origem BIGINT,
p_destino BIGINT,
p_valor NUMERIC
)
LANGUAGE plpgsql AS $$
BEGIN
UPDATE contas SET saldo = saldo - p_valor WHERE id = p_origem;
UPDATE contas SET saldo = saldo + p_valor WHERE id = p_destino;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE;
END;
$$;
-- Uso
CALL transferir_saldo(1, 2, 250.00);
Controle de fluxo PL/pgSQL
IF/ELSIF/ELSE, LOOP, FOR, WHILE, EXCEPTION
-- Condicional
IF condicao THEN ... ELSIF outra THEN ... ELSE ... END IF;
-- Loop numérico
FOR i IN 1..10 LOOP
INSERT INTO serie VALUES (i);
END LOOP;
-- Loop em query
FOR rec IN SELECT * FROM produtos LOOP
RAISE NOTICE 'Produto: %', rec.nome;
END LOOP;
-- While
WHILE contador < 10 LOOP
contador := contador + 1;
END LOOP;
-- Tratamento de erros
BEGIN
...
EXCEPTION
WHEN unique_violation THEN
RAISE WARNING 'Registro duplicado: %', SQLERRM;
WHEN OTHERS THEN
RAISE EXCEPTION 'Erro: %', SQLERRM;
END;
Índices
Otimização de consultas
Tipos de índice
B-Tree, Hash, GIN, GiST, BRIN — cada um otimiza padrões diferentes
-- B-Tree (padrão): igualdade, range, ORDER BY
CREATE INDEX idx_cliente_email ON clientes(email);
-- Composto
CREATE INDEX idx_pedido_cliente_data ON pedidos(cliente_id, criado_em DESC);
-- Parcial: indexa só parte das linhas
CREATE INDEX idx_pedidos_pendentes ON pedidos(cliente_id)
WHERE status = 'pendente';
-- GIN: arrays, JSONB, full-text search
CREATE INDEX idx_tags_gin ON posts USING GIN(tags);
CREATE INDEX idx_dados_gin ON logs USING GIN(payload jsonb_path_ops);
-- GiST: geometria, ranges
CREATE INDEX idx_geo ON locais USING GIST(coordenadas);
-- BRIN: tabelas muito grandes com dados correlacionados (time-series)
CREATE INDEX idx_logs_brin ON eventos USING BRIN(criado_em);
-- Concorrente: sem travar a tabela
CREATE INDEX CONCURRENTLY idx_novo ON tabela_grande(coluna);
-- Expressão
CREATE INDEX idx_lower_email ON clientes(lower(email));
-- Analisar uso de índices
EXPLAIN ANALYZE SELECT * FROM clientes WHERE email = 'a@b.com';
JSON / JSONB
Dados semi-estruturados
Operadores e funções JSON
Acesso, extração, atualização e pesquisa em colunas JSON/JSONB
-- Acesso
payload->'chave' -- retorna JSON
payload->>'chave' -- retorna TEXT
payload#>'{a,b}' -- caminho aninhado → JSON
payload#>>'{a,b}' -- caminho aninhado → TEXT
-- Exemplos
SELECT dados->>'nome' AS nome,
(dados->>'idade')::INT AS idade
FROM pessoas
WHERE dados @> '{"ativo": true}'; -- contém
-- Atualizar campo JSONB
UPDATE pessoas
SET dados = dados || '{"status": "vip"}'::jsonb
WHERE id = 1;
-- Remover chave
UPDATE pessoas SET dados = dados - 'campo_antigo' WHERE id = 1;
-- Desempacotar array
SELECT jsonb_array_elements(dados->'itens') AS item
FROM pedidos;
-- Construir JSON
SELECT jsonb_build_object('id', id, 'nome', nome) FROM clientes;
SELECT json_agg(c) FROM clientes c; -- agrega em array JSON
Views & Materialized Views
Consultas salvas e cacheadas
CREATE VIEW / MATERIALIZED VIEW
View é sempre atualizada; Materialized armazena fisicamente e precisa ser refrescada
-- View simples
CREATE OR REPLACE VIEW vw_clientes_ativos AS
SELECT id, nome, email, criado_em
FROM clientes
WHERE ativo = TRUE;
-- Materialized View
CREATE MATERIALIZED VIEW mv_relatorio_mensal AS
SELECT
date_trunc('month', criado_em) AS mes,
COUNT(*) AS total_pedidos,
SUM(valor) AS receita
FROM pedidos
GROUP BY 1
WITH DATA;
-- Refresca os dados (pode ser agendado via pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_relatorio_mensal;
-- Index em Materialized View
CREATE UNIQUE INDEX ON mv_relatorio_mensal(mes);
Permissões (DCL)
Data Control Language
GRANT / REVOKE / ROLE
Concede e revoga privilégios a usuários e papéis
-- Criar role/usuário
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD 'senha_segura';
-- Conceder permissões
GRANT CONNECT ON DATABASE meu_banco TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;
GRANT INSERT, UPDATE, DELETE ON pedidos TO app_user;
GRANT EXECUTE ON FUNCTION calcular_desconto(numeric,numeric) TO app_user;
-- Herdar role
GRANT app_readonly TO app_user;
-- Revogar
REVOKE DELETE ON pedidos FROM app_user;
-- Default privileges (futuras tabelas)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO app_readonly;
Manutenção & Diagnóstico
Performance, análise e administração
EXPLAIN / ANALYZE / VACUUM / ANALYZE
Plano de execução, estatísticas e manutenção de tabelas
-- Ver plano de execução
EXPLAIN SELECT * FROM clientes WHERE email = 'x@y.com';
-- Executar e mostrar tempo real
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pedidos WHERE valor > 1000;
-- Manutenção
VACUUM clientes; -- libera espaço
VACUUM FULL clientes; -- reescreve a tabela (bloqueia)
VACUUM ANALYZE clientes; -- vacuum + atualiza stats
ANALYZE clientes; -- só estatísticas
REINDEX TABLE clientes; -- reconstrói índices
CLUSTER clientes USING idx_email; -- ordena fisicamente
Monitoramento — pg_stat_* / locks
Queries ativas, bloqueios, uso de índices, tabelas inchadas
-- Queries em execução
SELECT pid, age(clock_timestamp(), query_start) AS duracao,
state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duracao DESC;
-- Cancelar / terminar query
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);
-- Bloqueios
SELECT * FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted;
-- Uso de índices
SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan;
-- Tamanho de tabelas
SELECT pg_size_pretty(pg_total_relation_size(relid)) AS tamanho,
relname
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
Tipos de Dados
Referência rápida