Cheatsheet PostgreSQL

SELECT * FROM clientes;

SELECT nome, email FROM clientes;

SELECT COUNT(*) FROM clientes;

SELECT * retorna todas as colunas. Especifique colunas para melhor performance. COUNT(*) conta linhas. Evite * em produção — liste só o necessário.

SELECT * FROM produtos
LIMIT 10;

SELECT * FROM produtos
LIMIT 10 OFFSET 20;

-- Página 3 (20 por página):
SELECT * FROM produtos
ORDER BY id
LIMIT 20 OFFSET 40;

LIMIT restringe o número de linhas. OFFSET salta N linhas (paginação). Sempre use ORDER BY com paginação para resultados consistentes. Para datasets grandes, prefira keyset pagination.

SELECT nome FROM clientes
UNION
SELECT nome FROM fornecedores;

SELECT nome FROM clientes
UNION ALL
SELECT nome FROM fornecedores;

UNION combina resultados removendo duplicados. UNION ALL mantém todos (mais rápido). Ambas as queries devem ter o mesmo número de colunas e tipos compatíveis. Prefira UNION ALL se não precisa de deduplicação.

SELECT nome AS cliente, email AS contacto
FROM clientes AS c;

SELECT c.nome, p.total
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id;

AS renomeia colunas ou tabelas temporariamente. O alias de tabela (c) simplifica JOINs. Em PostgreSQL, AS é opcional para tabelas mas recomendado para clareza.

SELECT nome || ' ' || apelido AS completo
FROM clientes;

SELECT CONCAT(nome, ' ', apelido)
FROM clientes;

SELECT FORMAT('Olá, %s!', nome)
FROM clientes;

O operador || concatena strings. CONCAT() ignora NULLs automaticamente. FORMAT() usa placeholders estilo %s. Se um operando de || for NULL, o resultado é NULL.

-- Alternativa standard ao LIMIT:
SELECT * FROM produtos
ORDER BY preco DESC
FETCH FIRST 10 ROWS ONLY;

SELECT * FROM produtos
ORDER BY id
OFFSET 20 ROWS
FETCH NEXT 10 ROWS ONLY;

FETCH FIRST N ROWS ONLY é o padrão SQL (equivalente a LIMIT). OFFSET ... ROWS substitui OFFSET. Ambos funcionam no PostgreSQL. Útil para portabilidade entre SGBDs. Requer ORDER BY.

SELECT DISTINCT cidade FROM clientes;

SELECT DISTINCT ON (cidade)
    cidade, nome
FROM clientes
ORDER BY cidade, criado_em DESC;

DISTINCT remove linhas duplicadas. DISTINCT ON é exclusivo do PostgreSQL — retorna a primeira linha de cada grupo. Combine com ORDER BY para controlar qual linha é escolhida.

SELECT preco * quantidade AS total
FROM itens;

SELECT preco * 1.23 AS com_iva
FROM produtos;

SELECT ROUND(preco * 1.23, 2) AS arredondado
FROM produtos;

Operadores +, -, *, / funcionam em colunas numéricas. ROUND() controla casas decimais. Use NUMERIC para dinheiro — nunca REAL ou FLOAT para valores monetários.

SELECT * FROM produtos
ORDER BY preco DESC;

SELECT * FROM clientes
ORDER BY cidade ASC, nome ASC;

SELECT * FROM pedidos
ORDER BY criado_em DESC NULLS LAST;

ORDER BY ordena resultados. ASC é o padrão, DESC inverte. NULLS LAST coloca nulos no fim (por defeito em ASC são primeiro). Múltiplas colunas separam-se por vírgula.

SELECT nome FROM clientes
WHERE id IN (
    SELECT cliente_id FROM pedidos
    WHERE total > 100
);

SELECT nome, (
    SELECT COUNT(*) FROM pedidos p
    WHERE p.cliente_id = c.id
) AS num_pedidos
FROM clientes c;

Subconsultas são queries dentro de queries. IN (SELECT ...) filtra por conjunto. Subconsultas correlacionadas referenciam a query externa (c.id). Para performance, prefira JOINs ou CTEs quando possível.

SELECT * FROM produtos
WHERE preco > 100;

SELECT * FROM clientes
WHERE ativo = true;

SELECT * FROM pedidos
WHERE total >= 50.00;

WHERE filtra linhas antes de retornar. Operadores: =, >, <, >=, <=, <> (diferente). Booleanos podem omitir = true: WHERE ativo.

WHERE nome LIKE 'Ana%';     -- começa com
WHERE nome LIKE '%silva%';  -- contém
WHERE nome LIKE '_na';      -- 2ª letra = n
WHERE nome ILIKE 'ana%';    -- case-insensitive

-- % = qualquer sequência
-- _ = exactamente um caractere

LIKE faz pattern matching com wildcards. % = zero ou mais chars, _ = um char. ILIKE é exclusivo do PostgreSQL — ignora maiúsculas/minúsculas. Para performance, use índice pg_trgm.

WHERE codigo SIMILAR TO '[0-9]{4}-[A-Z]{2}';
WHERE nome SIMILAR TO '%(silva|santos)%';

-- Combina LIKE + regex:
-- % e _ funcionam como LIKE
-- | [] () funcionam como regex

-- Alternativa mais simples:
WHERE codigo ~ '^\d{4}-[A-Z]{2}$';

SIMILAR TO é um híbrido entre LIKE e regex. Suporta %, _, |, []. Menos usado que ~ na prática. Para padrões complexos, prefira regex pura com ~.

SELECT * FROM produtos
WHERE ativo = true
  AND (preco < 50 OR stock > 0);

SELECT * FROM clientes
WHERE NOT cidade = 'Lisboa';

WHERE ativo AND preco BETWEEN 10 AND 100;

AND exige todas as condições. OR basta uma. Use parênteses para controlar precedência — AND tem prioridade sobre OR. NOT nega a condição.

SELECT * FROM clientes
WHERE telefone IS NULL;

SELECT * FROM clientes
WHERE email IS NOT NULL;

-- COALESCE para valor por defeito:
SELECT COALESCE(telefone, 'sem número')
FROM clientes;

NULL não se compara com = — use IS NULL / IS NOT NULL. NULL = NULL retorna NULL, não true. COALESCE() substitui NULL por um valor. Essencial para dados opcionais.

WHERE criado_em >= CURRENT_DATE - INTERVAL '7 days';
WHERE criado_em >= NOW() - INTERVAL '1 month';
WHERE EXTRACT(YEAR FROM criado_em) = 2024;
WHERE criado_em::date = CURRENT_DATE;

-- Últimos 30 dias:
WHERE criado_em > NOW() - INTERVAL '30 days';

INTERVAL faz aritmética temporal. CURRENT_DATE é a data actual. NOW() inclui hora. ::date converte timestamp para data. EXTRACT() obtém partes (ano, mês, dia). Índices funcionam melhor com comparações directas.

SELECT * FROM produtos
WHERE preco BETWEEN 10 AND 100;

SELECT * FROM pedidos
WHERE criado_em BETWEEN '2024-01-01' AND '2024-12-31';

-- Equivalente a:
WHERE preco >= 10 AND preco <= 100;

BETWEEN verifica intervalo inclusivo (inclui os limites). Funciona com números, datas e strings. Equivale a >= AND <=. Para datas, cuidado com o limite superior — use < 2025-01-01 para incluir o dia 31.

WHERE nome ~ '^[AB]';       -- começa com A ou B
WHERE nome ~* '^ana';       -- case-insensitive
WHERE nome !~ '[0-9]';      -- NÃO contém dígitos
WHERE email ~ '^[^@]+@[^@]+\.[^@]+$';

-- ~  = regex (case-sensitive)
-- ~* = regex (case-insensitive)
-- !~ = NOT regex

PostgreSQL suporta regex nativa com ~. ~* ignora o caso. !~ nega. Mais poderoso que LIKE para padrões complexos. Use ^ e $ para ancorar. Ideal para validação de formatos.

SELECT * FROM clientes
WHERE cidade IN ('Lisboa', 'Porto', 'Braga');

SELECT * FROM produtos
WHERE categoria_id NOT IN (3, 7, 9);

-- Com subconsulta:
WHERE id IN (SELECT cliente_id FROM pedidos);

IN verifica pertença a uma lista. NOT IN exclui valores. Aceita subconsultas. Cuidado: NOT IN com NULLs pode dar resultados inesperados — prefira NOT EXISTS nesses casos.

WHERE preco > ANY (SELECT preco FROM promocoes);
WHERE preco > ALL (SELECT preco FROM concorrentes);

WHERE EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = clientes.id
);

WHERE NOT EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = clientes.id
);

ANY compara com pelo menos um resultado. ALL exige todos. EXISTS verifica existência (mais eficiente que IN para subconsultas grandes). NOT EXISTS é seguro com NULLs.

SELECT p.nome, c.nome AS cliente
FROM pedidos p
INNER JOIN clientes c ON p.cliente_id = c.id;

-- Só retorna linhas com correspondência
-- em AMBAS as tabelas

INNER JOIN retorna só linhas com match em ambas as tabelas. ON define a condição de junção. Linhas sem correspondência são excluídas. É o JOIN mais comum. INNER é opcional — JOIN sozinho equivale.

SELECT f.nome AS funcionario,
       g.nome AS gestor
FROM funcionarios f
LEFT JOIN funcionarios g
    ON f.gestor_id = g.id;

SELF JOIN é um JOIN da tabela com ela mesma. Use aliases diferentes (f e g). Clássico para hierarquias (empregado → gestor). A FK referencia a própria PK.

SELECT p.id, c.nome AS cliente,
       pr.nome AS produto, p.quantidade
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
JOIN produtos pr ON pr.id = p.produto_id
JOIN categorias cat ON cat.id = pr.categoria_id
WHERE cat.nome = 'Electrónica';

Encadeie múltiplos JOINs para navegar relações. Cada ON liga uma FK à PK. A ordem dos JOINs pode afectar performance. O optimizador do PostgreSQL geralmente reordena automaticamente.

SELECT c.nome, p.total
FROM clientes c
LEFT JOIN pedidos p ON c.id = p.cliente_id;

-- Clientes sem pedidos aparecem
-- com p.total = NULL

LEFT JOIN mantém TODAS as linhas da tabela esquerda, mesmo sem match. Colunas da direita ficam NULL quando não há correspondência. Ideal para "todos os clientes, com ou sem pedidos".

SELECT c.nome, ult.total
FROM clientes c
CROSS JOIN LATERAL (
    SELECT total FROM pedidos p
    WHERE p.cliente_id = c.id
    ORDER BY criado_em DESC
    LIMIT 3
) ult;

LATERAL permite subconsultas correlacionadas no FROM. Cada linha de c executa a subconsulta. Ideal para "top N por grupo". Mais flexível que subconsultas normais. Requer CROSS JOIN ou LEFT JOIN antes.

-- USING: quando a coluna tem o mesmo nome
SELECT * FROM pedidos
JOIN clientes USING (cliente_id);

-- NATURAL: JOIN automático por colunas comuns
SELECT * FROM pedidos
NATURAL JOIN clientes;

-- Prefira ON explícito para clareza

USING (coluna) simplifica quando ambos os lados têm o mesmo nome. NATURAL JOIN faz JOIN automático por todas as colunas comuns — arriscado e pouco claro. Prefira sempre ON explícito para evitar surpresas.

SELECT c.nome, p.total
FROM clientes c
RIGHT JOIN pedidos p ON c.id = p.cliente_id;

SELECT *
FROM tabela_a a
FULL OUTER JOIN tabela_b b ON a.id = b.a_id;

RIGHT JOIN mantém todas da direita (raro — inverta a ordem). FULL OUTER JOIN mantém todas de ambas, com NULLs onde não há match. Útil para encontrar registos órfãos em qualquer lado.

SELECT cidade, COUNT(*) AS total
FROM clientes
GROUP BY cidade;

SELECT categoria, AVG(preco), MAX(preco)
FROM produtos
GROUP BY categoria;

GROUP BY agrupa linhas por valor. Funções agregadas (COUNT, AVG, MAX) aplicam-se a cada grupo. Colunas no SELECT devem estar no GROUP BY ou ser agregadas. Ordena com ORDER BY após.

SELECT t.tamanho, c.cor
FROM tamanhos t
CROSS JOIN cores c;

-- Produto cartesiano: todas as combinações
-- 3 tamanhos × 4 cores = 12 linhas

CROSS JOIN gera o produto cartesiano — cada linha de A com cada linha de B. Sem condição ON. Útil para gerar combinações (tamanhos × cores). Cuidado com tabelas grandes: N × M linhas.

SELECT cidade, COUNT(*) AS total
FROM clientes
GROUP BY cidade
HAVING COUNT(*) > 10;

-- WHERE filtra ANTES do agrupamento
-- HAVING filtra DEPOIS do agrupamento

HAVING filtra grupos (após GROUP BY). WHERE filtra linhas individuais (antes). Não pode usar alias no HAVING — repita a expressão. Equivalente a um WHERE para resultados agregados.

INSERT INTO clientes (nome, email, cidade)
VALUES ('Ana Silva', 'ana@mail.com', 'Lisboa');

-- Múltiplas linhas:
INSERT INTO produtos (nome, preco)
VALUES ('Teclado', 49.90),
       ('Rato', 29.90),
       ('Monitor', 299.00);

INSERT INTO ... VALUES insere linhas. Especifique as colunas explicitamente. Múltiplas linhas separam-se por vírgula — mais rápido que INSERTs separados. Colunas com DEFAULT ou SERIAL podem ser omitidas.

UPDATE produtos
SET preco = 99.90
WHERE id = 5;

UPDATE produtos
SET preco = preco * 1.10,
    actualizado_em = NOW()
WHERE categoria = 'Electrónica';

UPDATE ... SET modifica linhas existentes. Sempre use WHERE — sem ele, actualiza TUDO. Pode referenciar o valor actual (preco * 1.10). Múltiplas colunas separam-se por vírgula.

TRUNCATE TABLE logs;

TRUNCATE TABLE logs RESTART IDENTITY;

TRUNCATE TABLE pedidos, itens_pedido CASCADE;

TRUNCATE apaga TODAS as linhas instantaneamente (não regista linha a linha). RESTART IDENTITY reinicia sequências SERIAL. CASCADE apaga tabelas relacionadas. Muito mais rápido que DELETE para limpar tabelas inteiras.

INSERT INTO clientes (nome, email)
VALUES ('Ana', 'ana@mail.com')
RETURNING id;

INSERT INTO produtos (nome, preco)
VALUES ('Teclado', 49.90)
RETURNING id, criado_em;

RETURNING é exclusivo do PostgreSQL — retorna dados da linha inserida. Elimina a necessidade de um SELECT extra para obter o id gerado. Pode retornar qualquer coluna ou expressão. Essencial para APIs.

UPDATE produtos
SET ativo = false
WHERE stock = 0
RETURNING nome, id;

UPDATE clientes
SET desconto = 0.10
WHERE cidade = 'Porto'
RETURNING *;

RETURNING no UPDATE mostra as linhas afectadas. RETURNING * retorna todas as colunas. Útil para confirmar o que mudou sem SELECT extra. Funciona também com DELETE.

-- Exportar para CSV:
COPY clientes TO '/tmp/clientes.csv'
WITH (FORMAT csv, HEADER true);

-- Importar de CSV:
COPY clientes (nome, email)
FROM '/tmp/clientes.csv'
WITH (FORMAT csv, HEADER true);

-- Via psql:
-- \copy clientes TO 'file.csv' CSV HEADER

COPY é a forma mais rápida de importar/exportar dados. FORMAT csv para CSV. HEADER true inclui/ignora cabeçalho. \copy no psql opera no cliente (não precisa de superuser). Milhares de vezes mais rápido que INSERTs individuais.

INSERT INTO produtos (id, nome, stock)
VALUES (1, 'Teclado', 10)
ON CONFLICT (id) DO UPDATE
SET stock = produtos.stock + EXCLUDED.stock;

-- Ou ignorar:
ON CONFLICT (id) DO NOTHING;

ON CONFLICT implementa UPSERT (insert ou update). DO UPDATE SET actualiza se existir conflito. EXCLUDED referencia os valores que iam ser inseridos. DO NOTHING ignora silenciosamente. Requer constraint UNIQUE ou PK.

UPDATE pedidos p
SET estado = 'cancelado'
FROM clientes c
WHERE p.cliente_id = c.id
  AND c.ativo = false;

-- Sintaxe exclusiva do PostgreSQL
-- (não usa UPDATE ... JOIN)

PostgreSQL usa FROM para JOINs no UPDATE (não JOIN). A tabela actualizada não se repete no FROM. Use alias para clareza. Ideal para actualizações baseadas em dados de outra tabela.

INSERT INTO clientes_arquivo (nome, email)
SELECT nome, email FROM clientes
WHERE ativo = false;

INSERT INTO relatorio (cidade, total)
SELECT cidade, COUNT(*)
FROM clientes
GROUP BY cidade;

INSERT ... SELECT copia dados de uma query. Não use VALUES — o SELECT fornece as linhas. As colunas devem ser compatíveis em tipo e ordem. Ideal para migrações e snapshots.

DELETE FROM clientes
WHERE ativo = false;

DELETE FROM pedidos
WHERE criado_em < NOW() - INTERVAL '2 years';

DELETE FROM logs
RETURNING id;

DELETE FROM remove linhas. Sempre use WHERE — sem ele, apaga TUDO. RETURNING mostra o que foi apagado. Respeita FKs com ON DELETE. Para apagar tudo, prefira TRUNCATE.

CREATE TABLE produtos (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    preco NUMERIC(8,2) DEFAULT 0,
    ativo BOOLEAN DEFAULT true,
    criado_em TIMESTAMP DEFAULT NOW()
);

SERIAL cria auto-incremento (sequência). PRIMARY KEY define a chave. NOT NULL obriga preenchimento. DEFAULT define valor por omissão. NUMERIC(8,2) para dinheiro com 2 decimais.

CREATE TABLE sessoes (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    user_id INTEGER NOT NULL
);

-- Gerar manualmente:
SELECT gen_random_uuid();

UUID gera identificadores únicos universais (128-bit). gen_random_uuid() é nativo desde PostgreSQL 13. Ideal para APIs e sistemas distribuídos. Ocupa 16 bytes (vs 4 do INTEGER). Não é sequencial — pior para índices.

cliente_id INT REFERENCES clientes(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

-- Opções:
-- CASCADE: propaga delete/update
-- SET NULL: coloca NULL
-- RESTRICT: impede (padrão)
-- NO ACTION: verifica no fim

REFERENCES cria a FK. ON DELETE CASCADE apaga filhos automaticamente. SET NULL anula a FK. RESTRICT (padrão) impede delete se existirem filhos. Escolha conforme a regra de negócio.

SMALLINT          -- -32768 a 32767
INTEGER           -- -2B a 2B (padrão)
BIGINT            -- muito grande
NUMERIC(10,2)     -- exacto (dinheiro)
REAL              -- 6 dígitos (float)
DOUBLE PRECISION  -- 15 dígitos

-- NUMERIC é exacto, REAL é aproximado

INTEGER é o padrão para IDs e contagens. NUMERIC é exacto — use para dinheiro. REAL/DOUBLE são aproximados (erros de arredondamento). BIGINT para valores muito grandes. Nunca use float para valores monetários.

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

INSERT INTO posts (tags)
VALUES (ARRAY['sql', 'postgres']);

WHERE 'sql' = ANY(tags);
WHERE tags @> ARRAY['sql'];

TEXT[] cria coluna de array. ANY() verifica se contém um valor. @> verifica se contém todos os valores do array. Alternativa a tabelas de relação N:N para dados simples. Índices GIN aceleram buscas em arrays.

ALTER TABLE clientes
    ADD COLUMN telefone VARCHAR(20);

ALTER TABLE clientes
    DROP COLUMN fax;

ALTER TABLE clientes
    ALTER COLUMN nome SET NOT NULL;

ALTER TABLE clientes
    RENAME COLUMN nome TO nome_completo;

ALTER TABLE modifica estrutura existente. ADD COLUMN adiciona, DROP COLUMN remove. ALTER COLUMN ... SET NOT NULL adiciona constraint. RENAME renomeia. Em tabelas grandes, ADD COLUMN com DEFAULT pode ser lento.

CHAR(10)       -- tamanho fixo (completa espaços)
VARCHAR(255)   -- tamanho variável (máx 255)
TEXT           -- sem limite (eficiente!)

-- No PostgreSQL, TEXT = VARCHAR sem limite
-- Sem penalização de performance

TEXT é a escolha padrão no PostgreSQL — sem limite e sem penalização. VARCHAR(n) limita o tamanho (validação). CHAR(n) completa com espaços (raro). Ao contrário de outros SGBDs, TEXT é tão rápido quanto VARCHAR.

DATE              -- 2024-01-15
TIME              -- 14:30:00
TIMESTAMP         -- 2024-01-15 14:30:00
TIMESTAMPTZ       -- com timezone
INTERVAL          -- duração ('7 days')

criado_em TIMESTAMPTZ DEFAULT NOW()

TIMESTAMPTZ guarda com timezone — prefira sempre este. TIMESTAMP sem timezone é ambíguo. DATE só data, TIME só hora. INTERVAL para durações. NOW() retorna timestamp actual com tz.

ativo BOOLEAN DEFAULT true

-- Valores aceites:
-- true, 't', 'yes', '1'
-- false, 'f', 'no', '0'

WHERE ativo = true;
WHERE ativo;          -- equivalente
WHERE NOT ativo;      -- negação

BOOLEAN armazena true/false/null. PostgreSQL aceita várias representações (t/f, yes/no). WHERE ativo é equivalente a WHERE ativo = true. NULL é distinto de false — use IS NOT TRUE para incluir NULLs.

CREATE TABLE produtos (
    id SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    preco NUMERIC CHECK (preco >= 0),
    categoria_id INT REFERENCES categorias(id),
    CONSTRAINT nome_unico UNIQUE (nome, categoria_id)
);

PRIMARY KEY = UNIQUE + NOT NULL. UNIQUE impede duplicados. CHECK valida condições. REFERENCES cria FK. CONSTRAINT nome dá nome explícito para facilitar DROP. Constraints são validadas em cada INSERT/UPDATE.

WITH clientes_ativos AS (
    SELECT id, nome FROM clientes
    WHERE ativo = true
)
SELECT ca.nome, COUNT(p.id) AS pedidos
FROM clientes_ativos ca
JOIN pedidos p ON p.cliente_id = ca.id
GROUP BY ca.nome;

WITH cria uma CTE (Common Table Expression) — consulta temporária nomeada. Melhora legibilidade de queries complexas. Existe só durante a query. Pode ser referenciada múltiplas vezes. Não é materializada por defeito (PostgreSQL 12+).

CREATE TABLE eventos (
    id SERIAL PRIMARY KEY,
    dados JSONB
);

INSERT INTO eventos (dados) VALUES
('{"tipo": "click", "pagina": "/home"}'),
('{"tipo": "view", "duracao": 30}');

JSONB armazena JSON em formato binário (mais rápido que JSON). Suporta índices e operadores de consulta. Ideal para dados semi-estruturados. Valida o JSON na inserção. Prefira JSONB a JSON na maioria dos casos.

ALTER TABLE posts ADD COLUMN busca tsvector
    GENERATED ALWAYS AS (
        to_tsvector('portuguese', titulo || ' ' || corpo)
    ) STORED;

CREATE INDEX idx_busca ON posts USING GIN (busca);

SELECT * FROM posts
WHERE busca @@ to_tsquery('portuguese', 'sql & tutorial');

tsvector converte texto para tokens de pesquisa. to_tsquery cria a query de busca. @@ é o operador de match. Índice GIN acelera. Suporta stemming e ranking. Alternativa nativa a Elasticsearch para buscas simples.

WITH RECURSIVE hierarquia AS (
    SELECT id, nome, gestor_id, 1 AS nivel
    FROM funcionarios WHERE gestor_id IS NULL
    UNION ALL
    SELECT f.id, f.nome, f.gestor_id, h.nivel + 1
    FROM funcionarios f
    JOIN hierarquia h ON f.gestor_id = h.id
)
SELECT * FROM hierarquia;

WITH RECURSIVE permite recursão em SQL. A parte âncora inicia, a recursiva referencia a própria CTE. UNION ALL combina resultados. Ideal para hierarquias, árvores e grafos. Sempre inclua condição de paragem.

SELECT dados->>'tipo' AS tipo
FROM eventos;

SELECT * FROM eventos
WHERE dados @> '{"tipo": "click"}';

SELECT dados->'user'->>'nome'
FROM eventos;

-- Caminho aninhado:
SELECT dados #>> '{user, address, city}'
FROM eventos;

->> extrai valor como texto. -> extrai como JSON. @> verifica contenção (usa índice GIN). #>> acede a caminhos aninhados. Combine com WHERE para filtrar documentos JSON eficientemente.

BEGIN;

UPDATE contas SET saldo = saldo - 100
WHERE id = 1;

UPDATE contas SET saldo = saldo + 100
WHERE id = 2;

COMMIT;
-- Ou ROLLBACK; para anular tudo

BEGIN inicia transacção. COMMIT confirma, ROLLBACK anula. Garante atomicidade — tudo ou nada. Essencial para operações multi-tabela. SAVEPOINT permite rollback parcial. PostgreSQL usa MVCC por defeito.

SELECT nome, salario,
    RANK() OVER (ORDER BY salario DESC) AS rank,
    AVG(salario) OVER () AS media_geral
FROM funcionarios;

-- PARTITION BY agrupa a janela:
SELECT dept, nome, salario,
    RANK() OVER (PARTITION BY dept ORDER BY salario DESC)
FROM funcionarios;

OVER() define a janela. ORDER BY dentro de OVER ordena. PARTITION BY reinicia o cálculo por grupo. Não colapsa linhas como GROUP BY. RANK(), ROW_NUMBER(), DENSE_RANK() são as mais comuns.

CREATE INDEX idx_email ON clientes(email);

CREATE INDEX idx_dados ON eventos USING GIN (dados);

CREATE UNIQUE INDEX idx_slug ON posts(slug);

CREATE INDEX idx_criado ON pedidos(criado_em DESC);

DROP INDEX idx_email;

CREATE INDEX acelera WHERE e ORDER BY. B-tree é o padrão (comparações). GIN para JSONB, arrays e full-text. UNIQUE impede duplicados. Índices aceleram leitura mas abrandam escrita. Não crie em excesso.

-- Sequência de números:
SELECT * FROM generate_series(1, 10);

-- Sequência com passo:
SELECT * FROM generate_series(0, 100, 10);

-- Sequência de datas (útil para relatórios):
SELECT d::date
FROM generate_series(
  '2024-01-01'::date,
  '2024-12-31'::date,
  '1 month'::interval
) AS d;

-- Preencher gaps: LEFT JOIN com a série
-- garante todos os dias/meses no resultado

generate_series() gera sequências de números, datas ou timestamps. Essencial para relatórios sem gaps — faça LEFT JOIN da série com os dados para incluir períodos sem registos. Aceita passo (incremento) como terceiro argumento.

SELECT nome, salario,
    ROW_NUMBER() OVER (ORDER BY id) AS num,
    LAG(salario) OVER (ORDER BY id) AS anterior,
    LEAD(salario) OVER (ORDER BY id) AS proximo
FROM funcionarios;

-- Diferença com o anterior:
SELECT salario - LAG(salario) OVER (ORDER BY id)
FROM funcionarios;

ROW_NUMBER() numera sequencialmente. LAG() acede à linha anterior, LEAD() à seguinte. Ideais para comparações temporais e deltas. Aceitam offset: LAG(col, 2) = 2 linhas atrás.

CREATE VIEW clientes_ativos AS
SELECT id, nome, email FROM clientes
WHERE ativo = true;

CREATE MATERIALIZED VIEW relatorio_vendas AS
SELECT cidade, SUM(total) FROM pedidos
GROUP BY cidade;

REFRESH MATERIALIZED VIEW relatorio_vendas;

VIEW é uma query guardada (executa a cada acesso). MATERIALIZED VIEW guarda o resultado fisicamente — mais rápido mas desactualizado. REFRESH actualiza os dados. Ideal para relatórios pesados consultados frequentemente.

-- Agregar só um subconjunto de linhas:
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE estado = 'pago') AS pagos,
  SUM(total) FILTER (WHERE estado = 'pago') AS receita,
  AVG(total) FILTER (WHERE total > 0) AS media
FROM pedidos;

-- Equivalente a CASE dentro do agregado,
-- mas mais legível:
-- SUM(CASE WHEN estado='pago' THEN total END)

-- Funciona com qualquer agregado:
-- COUNT, SUM, AVG, array_agg, etc.

FILTER (WHERE ...) aplica um agregado só às linhas que cumprem a condição — mais legível que CASE dentro do agregado. Funciona com COUNT, SUM, AVG, array_agg, etc. Exclusivo do PostgreSQL/SQL padrão.

SELECT
    COUNT(*) AS total,
    SUM(preco) AS soma,
    AVG(preco) AS media,
    MIN(preco) AS minimo,
    MAX(preco) AS maximo
FROM produtos;

COUNT(*) conta linhas. SUM, AVG, MIN, MAX operam em colunas numéricas. Ignoram NULLs (excepto COUNT(*)). Combine com GROUP BY para agregações por grupo.

SELECT '123'::INTEGER;
SELECT 123::TEXT;
SELECT '2024-01-15'::DATE;
SELECT preco::NUMERIC(10,2);

-- Equivalente standard:
SELECT CAST('123' AS INTEGER);

:: é a sintaxe PostgreSQL para conversão de tipos. Equivale a CAST(x AS tipo). Converte strings para números, datas, etc. Se a conversão falhar, gera erro. Útil para comparar tipos diferentes.

SELECT GREATEST(10, 20, 5);   -- 20
SELECT LEAST(10, 20, 5);      -- 5

SELECT WIDTH_BUCKET(preco, 0, 100, 5)
FROM produtos;  -- bucket 1-5

-- IIF não existe; use CASE ou:
SELECT (preco > 100)::TEXT FROM produtos;

GREATEST/LEAST retornam o maior/menor de uma lista. WIDTH_BUCKET distribui em intervalos. PostgreSQL não tem IIF() — use CASE. Cast booleano para texto retorna true/false.

SELECT STRING_AGG(nome, ', ' ORDER BY nome)
FROM clientes;

SELECT ARRAY_AGG(DISTINCT cidade)
FROM clientes;

-- Resultado: "Ana, Bruno, Carla"
-- Resultado: {Lisboa, Porto, Braga}

STRING_AGG() concatena valores com separador. ARRAY_AGG() colecta valores num array. Ambos aceitam ORDER BY interno. DISTINCT remove duplicados. Úteis para relatórios e listas.

SELECT COALESCE(telefone, 'sem número')
FROM clientes;

SELECT COALESCE(a, b, c, 'default');

SELECT NULLIF(preco, 0);
-- Retorna NULL se preco = 0

-- Evitar divisão por zero:
SELECT total / NULLIF(qtd, 0) FROM itens;

COALESCE() retorna o primeiro valor não-NULL. NULLIF(a, b) retorna NULL se a = b. Combine para evitar divisão por zero. COALESCE aceita múltiplos argumentos. Essencial para valores por defeito.

SELECT current_database();
SELECT current_user;
SELECT version();
SELECT pg_size_pretty(pg_database_size('loja'));
SELECT pg_backend_pid();

-- Tamanho de tabela:
SELECT pg_size_pretty(pg_total_relation_size('pedidos'));

current_database() e current_user mostram contexto. version() retorna a versão do PostgreSQL. pg_size_pretty() formata tamanhos legíveis. pg_total_relation_size() inclui índices. Úteis para administração e monitoring.

UPPER('olá')           -- 'OLÁ'
LOWER('OLÁ')           -- 'olá'
LENGTH('olá')          -- 3
TRIM('  olá  ')        -- 'olá'
SUBSTRING('olá' FROM 1 FOR 2)  -- 'ol'
REPLACE('olá', 'á', 'a')       -- 'ola'
INITCAP('ana silva')   -- 'Ana Silva'

UPPER/LOWER convertem caso. LENGTH conta caracteres. TRIM remove espaços. SUBSTRING extrai parte. INITCAP capitaliza cada palavra. PostgreSQL usa posição baseada em 1.

SELECT nome,
    CASE
        WHEN preco > 100 THEN 'caro'
        WHEN preco > 50 THEN 'médio'
        ELSE 'barato'
    END AS categoria
FROM produtos;

-- Forma simples:
CASE status WHEN 'A' THEN 'Ativo'
            WHEN 'I' THEN 'Inactivo' END

CASE WHEN implementa lógica condicional no SELECT. Funciona como if/else. A forma simples compara um valor. Sempre termine com END. Pode usar em WHERE, ORDER BY e GROUP BY. Retorna NULL se nenhum caso bater e não houver ELSE.

NOW()                          -- timestamp actual
CURRENT_DATE                   -- data actual
EXTRACT(YEAR FROM criado_em)   -- 2024
TO_CHAR(criado_em, 'DD/MM/YYYY')  -- '15/01/2024'
DATE_TRUNC('month', criado_em)    -- 1º do mês
criado_em + INTERVAL '7 days'     -- +7 dias

NOW() retorna timestamp com timezone. EXTRACT() obtém partes (YEAR, MONTH, DAY). TO_CHAR() formata como string. DATE_TRUNC() arredonda para unidade. Aritmética com INTERVAL é intuitiva.

ROUND(3.14159, 2)   -- 3.14
CEIL(4.1)           -- 5
FLOOR(4.9)          -- 4
ABS(-5)             -- 5
MOD(10, 3)          -- 1
POWER(2, 10)        -- 1024
RANDOM()            -- 0.0 a 1.0

ROUND() arredonda a N casas. CEIL/FLOOR para cima/baixo. ABS() valor absoluto. MOD() resto da divisão. RANDOM() gera float aleatório. Use ORDER BY RANDOM() para amostras (lento em tabelas grandes).

psql -U postgres -d minha_bd
psql -h localhost -p 5432 -U user -d bd

# Com password (pede interactivamente):
psql -U postgres -W -d bd

# Connection string:
psql "postgresql://user:pass@host:5432/bd"

psql é o cliente CLI do PostgreSQL. -U utilizador, -d base de dados, -h host, -p porta. -W força pedido de password. Connection strings são práticas para scripts e automação.

# Backup SQL:
pg_dump -U postgres loja > backup.sql

# Backup custom (comprimido, selectivo):
pg_dump -U postgres -Fc loja > backup.dump

# Restaurar SQL:
psql -U postgres loja < backup.sql

# Restaurar custom:
pg_restore -U postgres -d loja backup.dump

pg_dump exporta uma BD. -Fc formato custom (comprimido, permite restore selectivo). pg_restore importa formato custom. pg_dumpall exporta todas as BDs. Sempre teste restores. Agende backups com cron.

VACUUM ANALYZE clientes;

VACUUM FULL clientes;  -- reescreve tabela (lock)

ANALYZE clientes;      -- só estatísticas

-- Autovacuum (configuração):
-- autovacuum = on (padrão)
-- autovacuum_naptime = 60s

VACUUM reclaima espaço de linhas mortas (MVCC). ANALYZE actualiza estatísticas para o planner. VACUUM FULL reescreve a tabela (lock exclusivo — evite em produção). autovacuum corre automaticamente por defeito.

\l          -- listar databases
\dt         -- listar tabelas
\d tabela   -- descrever tabela
\du         -- listar roles
\di         -- listar índices
\dn         -- listar schemas
\x          -- modo expandido
\timing     -- mostrar tempo de execução
\q          -- sair

Meta-comandos começam com \ (não são SQL). \dt lista tabelas, \d tabela mostra estrutura. \timing mostra duração de cada query. \x alterna formato vertical (útil para linhas largas). \? mostra ajuda.

CREATE SCHEMA vendas;

CREATE TABLE vendas.pedidos (
    id SERIAL PRIMARY KEY
);

SET search_path TO vendas, public;

SELECT * FROM vendas.pedidos;

SCHEMA organiza tabelas em namespaces lógicos. public é o schema padrão. search_path define a ordem de procura. Útil para multi-tenancy ou separação de módulos. Permissões podem ser por schema.

CREATE TABLESPACE ssd LOCATION '/mnt/ssd/pgdata';

CREATE TABLE hot_data (...)
    TABLESPACE ssd;

-- WAL (Write-Ahead Log):
-- pg_wal/ contém logs de transacções
-- archive_mode = on  (para PITR)
-- wal_level = replica (para streaming)

TABLESPACE permite armazenar dados em discos diferentes. Útil para separar dados quentes/frios. WAL garante durabilidade — escreve antes dos dados. archive_mode para point-in-time recovery. Essencial para HA e disaster recovery.

CREATE DATABASE loja
    ENCODING 'UTF8'
    LC_COLLATE 'pt_PT.UTF-8'
    TEMPLATE template0;

-- Ligar:
\c loja

-- Apagar:
DROP DATABASE loja;

CREATE DATABASE cria nova BD. ENCODING UTF8 é o padrão recomendado. TEMPLATE0 para encoding diferente do template. \c muda de BD no psql. DROP DATABASE é irreversível — cuidado.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Listar disponíveis:
SELECT * FROM pg_available_extensions;

-- Listar instaladas:
SELECT * FROM pg_extension;

CREATE EXTENSION activa funcionalidades extra. pg_trgm para pesquisa fuzzy. postgis para dados geográficos. uuid-ossp para UUIDs (legacy). Requer superuser. Extensões são instaladas por BD.

# Backup lógico (SQL):
pg_dump -U postgres -d loja > loja.sql

# Formato custom (binário, compressão):
pg_dump -U postgres -Fc loja > loja.dump

# Restore do formato custom:
pg_restore -U postgres -d loja loja.dump

# Só o schema (sem dados):
pg_dump -U postgres --schema-only loja > schema.sql

# Backup de TODAS as bases de dados:
pg_dumpall -U postgres > todas.sql

# pg_dump não bloqueia leituras

pg_dump faz backup lógico sem bloquear leituras. O formato -Fc (custom) é binário, comprimido e permite restore seletivo com pg_restore. --schema-only exporta só a estrutura. pg_dumpall copia todas as bases de dados.

CREATE ROLE ana WITH LOGIN PASSWORD 'senha';
CREATE ROLE admin WITH SUPERUSER;

GRANT ALL PRIVILEGES ON DATABASE loja TO ana;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO ana;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO ana;

REVOKE DELETE ON clientes FROM ana;

CREATE ROLE cria utilizador/grupo. WITH LOGIN permite conexão. GRANT dá permissões, REVOKE remove. SUPERUSER tem acesso total. Permissões são por objecto (tabela, schema, sequência).

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_user_tables;

-- Queries lentas activas:
SELECT pid, query, state, query_start
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Cancelar query:
SELECT pg_cancel_backend(pid);

pg_stat_activity mostra conexões e queries activas. pg_stat_user_tables estatísticas de uso. pg_cancel_backend() cancela uma query. pg_terminate_backend() mata a conexão. Essenciais para debugging em produção.

# Ficheiro: data/pg_hba.conf
# Controla quem pode ligar e como

# TYPE  DATABASE  USER  ADDRESS        METHOD
local   all       all                  peer
host    all       all   127.0.0.1/32   scram-sha-256
host    loja      ana   192.168.1.0/24 scram-sha-256
host    all       all   0.0.0.0/0      reject

# Recarregar sem reiniciar:
SELECT pg_reload_conf();

pg_hba.conf controla o acesso: tipo (local/host), BD, utilizador, endereço e método. scram-sha-256 é o método de password recomendado. peer usa o utilizador do SO. pg_reload_conf() aplica mudanças sem reiniciar o serviço.

EXPLAIN ANALYZE
SELECT * FROM produtos
WHERE preco > 100;

-- Procurar por:
-- "Seq Scan" = sem índice (lento)
-- "Index Scan" = usa índice (rápido)
-- "actual time" = tempo real
-- "rows" = linhas processadas

EXPLAIN mostra o plano de execução. ANALYZE executa e mostra tempos reais. Seq Scan indica falta de índice. Index Scan é o desejado. Verifique rows vs actual rows para estimativas erradas. Ferramenta #1 de optimização.

-- Strings: aspas simples
WHERE nome = 'Ana';

-- Identificadores: aspas duplas
SELECT "Nome" FROM "Clientes";

-- PostgreSQL converte para minúsculas:
CREATE TABLE Cliente  -- guarda como "cliente"
SELECT * FROM CLIENTE -- funciona (→ cliente)

Strings usam aspas simples. Identificadores usam aspas duplas (só se necessário). PostgreSQL normaliza para minúsculas — Cliente vira cliente. Evite nomes com maiúsculas ou palavras reservadas. Convenção: tudo em snake_case.

-- Nunca concatenar inputs:
-- "SELECT * FROM users WHERE id = " + input  ← MAL

-- Sempre parâmetros:
SELECT * FROM users WHERE id = $1;

-- Princípio do menor privilégio:
CREATE ROLE app WITH LOGIN PASSWORD 'x';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES
    IN SCHEMA public TO app;
-- Sem DELETE, sem DDL, sem superuser

Nunca concatene input do utilizador — use parâmetros ($1, ?). Previne SQL injection. Crie roles com privilégio mínimo para a aplicação. Não use superuser para a app. pg_hba.conf controla acesso por rede/IP.

-- Índice para WHERE frequente:
CREATE INDEX idx_ativo ON clientes(ativo);

-- Índice composto (ordem importa):
CREATE INDEX idx_cid_nome ON clientes(cidade, nome);

-- Índice parcial:
CREATE INDEX idx_ativos ON clientes(nome)
WHERE ativo = true;

Crie índices para colunas em WHERE, JOIN e ORDER BY. Índices compostos seguem a ordem das colunas. WHERE no índice cria índice parcial (menor, mais rápido). Cada índice abrandam INSERT/UPDATE. Equilibre leitura vs escrita.

-- OFFSET (lento para páginas grandes):
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 10000;

-- Keyset (rápido e consistente):
SELECT * FROM posts
WHERE id > 10000
ORDER BY id
LIMIT 20;

OFFSET salta linhas (lento para valores grandes). Keyset pagination usa WHERE com o último ID visto — sempre rápido. Prefira keyset para infinite scroll. OFFSET para paginação com números de página. Combine com índice na coluna de ordenação.

-- pg_trgm: pesquisa fuzzy e LIKE rápido
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_nome ON clientes
    USING GIN (nome gin_trgm_ops);

-- pg_stat_statements: queries lentas
CREATE EXTENSION pg_stat_statements;
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC LIMIT 10;

pg_trgm acelera LIKE e pesquisa fuzzy com índice GIN. pg_stat_statements regista todas as queries com tempos. Essencial para encontrar queries lentas em produção. Active shared_preload_libraries no postgresql.conf para pg_stat_statements.

PREPARE get_cliente (int) AS
    SELECT * FROM clientes WHERE id = $1;

EXECUTE get_cliente(42);

-- Em aplicações:
-- PHP: $pdo->prepare('SELECT * WHERE id = ?')
-- Node: client.query('SELECT * WHERE id = $1', [id])

PREPARE compila a query uma vez, executa muitas. $1, $2 são parâmetros. Previne SQL injection — nunca concatene inputs. O planner pode reutilizar o plano. Todas as linguagens têm suporte nativo.

-- IN (materializa subconsulta):
WHERE id IN (SELECT cliente_id FROM pedidos);

-- EXISTS (para na 1ª correspondência):
WHERE EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = clientes.id
);

-- NOT EXISTS (seguro com NULLs):
WHERE NOT EXISTS (SELECT 1 FROM ...);

EXISTS para na primeira correspondência — mais rápido para subconsultas grandes. IN materializa o conjunto todo. NOT EXISTS é seguro com NULLs (NOT IN não é). Para conjuntos pequenos, a diferença é mínima. O planner pode optimizar ambos.

-- Indexar o resultado de uma expressão:
CREATE INDEX idx_email_lower
ON clientes (LOWER(email));

-- A query tem de usar a MESMA expressão:
SELECT * FROM clientes
WHERE LOWER(email) = 'ana@exemplo.pt';

-- Útil para JSONB:
CREATE INDEX idx_meta ON eventos ((meta->>'tipo'));

-- Índice sobre cast de data:
CREATE INDEX idx_dia ON vendas ((created_at::date));

Índices de expressão indexam o resultado de uma função ou expressão, como LOWER(email). A query tem de usar a expressão exata para o índice ser aproveitado. Ideais para pesquisas case-insensitive e campos JSONB. Também funcionam com casts como ::date.

-- CORRECTO:
preco NUMERIC(10,2)

-- ERRADO:
preco REAL          -- erros de arredondamento!
preco DOUBLE PRECISION

-- Demonstração:
SELECT 0.1::REAL + 0.2::REAL;  -- 0.30000001
SELECT 0.1::NUMERIC + 0.2;     -- 0.3

NUMERIC é exacto — essencial para valores monetários. REAL/DOUBLE têm erros de representação binária. 0.1 + 0.2 != 0.3 com floats. NUMERIC(10,2) = 10 dígitos, 2 decimais. Nunca use float para dinheiro.

-- Tabelas: plural, snake_case
clientes, pedidos, itens_pedido

-- Colunas: snake_case
nome, criado_em, cliente_id

-- FKs: tabela_singular_id
cliente_id, produto_id

-- Índices: idx_tabela_coluna
idx_clientes_email

-- Constraints: pk_, fk_, uq_, chk_

Use snake_case para tudo (sem maiúsculas). Tabelas no plural (clientes). FKs como tabela_id. Índices com prefixo idx_. Constraints com prefixo descritivo. Evite palavras reservadas (order, group, user).

-- Indexar só um subconjunto de linhas:
CREATE INDEX idx_pedidos_ativos
ON pedidos (cliente_id)
WHERE estado = 'ativo';

-- Mais pequeno e rápido de manter
-- que um índice completo

-- O planificador usa-o quando a
-- query tem a mesma condição:
SELECT * FROM pedidos
WHERE estado = 'ativo' AND cliente_id = 42;

-- Ideal para flags (ativo/arquivado)
-- onde 90% das linhas são "mortas"

Índices parciais (CREATE INDEX ... WHERE condição) indexam só um subconjunto de linhas — mais pequenos e rápidos de manter. O planificador usa-os quando a query inclui a condição. Perfeitos para flags onde a maioria das linhas é irrelevante.