Referência SQL

SELECT col1, col2 FROM table_name;

Escolha colunas de uma tabela.

SELECT * FROM t
WHERE col = 5 AND status = 'active';

Filtra linhas: =, <>, <, >, AND, OR, IN, BETWEEN, LIKE.

SELECT * FROM t ORDER BY created_at DESC;

Ordena o resultado. ASC crescente (padrão), DESC decrescente.

SELECT * FROM t ORDER BY id LIMIT 10;

Limita o número de linhas retornadas.

SELECT * FROM a JOIN b ON a.id = b.a_id;

Apenas as linhas que têm correspondência em ambas as tabelas.

SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;

Todas as linhas da tabela à esquerda; NULL à direita se não houver correspondência.

SELECT u.name
FROM users u
JOIN orders o ON o.user_id = u.id;

Nomes curtos para as tabelas — obrigatórios quando as colunas têm o mesmo nome.

SELECT * FROM sizes CROSS JOIN colors;

Produto cartesiano — cada linha à esquerda com cada linha à direita. Para gerar todas as combinações.

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

Todas as linhas de ambas as tabelas; NULL onde não há correspondência. O MySQL não tem FULL JOIN — emule com LEFT ∪ RIGHT.

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Uma tabela unida a si mesma via alias — para hierarquias como «funcionário → gestor».

SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Linhas à esquerda SEM correspondência à direita — «usuários sem pedidos». Alternativa a NOT EXISTS.

SELECT COUNT(*), COUNT(email) FROM users;

Conta linhas de um grupo. COUNT(*) — todas as linhas, COUNT(col) — apenas não-NULL, COUNT(DISTINCT col) — valores únicos.

SELECT SUM(amount) FROM orders;

Soma de valores numéricos de um grupo. NULLs são ignorados. Retorna NULL (não 0) para grupo vazio.

SELECT AVG(price) FROM products;

Média aritmética. NULLs ficam fora do divisor. Converta uma coluna inteira para numeric ou a parte fracionária é truncada.

SELECT MIN(created_at) FROM orders;

Menor valor de um grupo. Funciona com números, datas e textos. NULLs são ignorados.

SELECT MAX(created_at) FROM orders;

Maior valor de um grupo. Funciona com números, datas e textos. NULLs são ignorados.

SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

Agrupa linhas. Toda coluna do SELECT que não seja de agregação deve estar no GROUP BY.

SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

Filtra sobre agregados — como WHERE, mas depois do GROUP BY.

SELECT DISTINCT country FROM users;

Remove as linhas duplicadas do resultado.

COUNT(*) FILTER (WHERE type = 'view') AS views

Agregado condicional: COUNT/SUM apenas sobre as linhas que satisfazem o WHERE. Substitui três consultas separadas por uma só.

STRING_AGG(name, ', ' ORDER BY created_at)

Concatena valores em uma única cadeia com um separador. O ORDER BY interno garante a ordem.

ARRAY_AGG(amount ORDER BY created_at)

Reúne valores em um array. Útil quando uma linha de auditoria precisa de todo o histórico em uma célula.

GROUP BY GROUPING SETS ((kind), (user_id), ())

Vários níveis de agrupamento em uma consulta — linha a linha: por kind, por user_id e um total geral.

GROUP BY ROLLUP (DATE_TRUNC('month', ts))

O mesmo nível de agrupamento mais uma linha de total geral (uma única linha NULL no final).

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)

Mediana / quantil. Mais resistente a valores atípicos do que AVG.

SELECT tag FROM articles, UNNEST(tags) tag

Expande um array em linhas — uma linha por elemento do array.

SELECT COUNT(DISTINCT user_id) FROM events;

Conta os valores distintos. Custoso em tabelas grandes — use APPROX_COUNT_DISTINCT / HLL quando uma estimativa basta.

SELECT BOOL_AND(active), BOOL_OR(is_admin) FROM users;

Agregados booleanos: BOOL_AND é true se TODAS as linhas forem true; BOOL_OR se ao menos uma for. NULLs são ignorados.

SELECT dept, EVERY(salary > 0) FROM emp GROUP BY dept;

O sinônimo padrão do SQL para BOOL_AND — true quando a condição vale para todas as linhas do grupo.

SELECT STDDEV_SAMP(amount), STDDEV_POP(amount) FROM orders;

Desvio padrão: _SAMP para uma amostra (divisor n-1), _POP para toda a população (divisor n). STDDEV sozinho equivale a STDDEV_SAMP.

SELECT VAR_SAMP(amount), VAR_POP(amount) FROM orders;

Variância — o quadrado do desvio padrão. _SAMP para amostra, _POP para população. VARIANCE sozinho equivale a VAR_SAMP.

SELECT MODE() WITHIN GROUP (ORDER BY status) FROM tickets;

A moda — o valor mais frequente do grupo. Em caso de empate vence o primeiro pelo ORDER BY.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) FROM orders;

Percentil discreto — retorna um valor que realmente existe nos dados, diferente de PERCENTILE_CONT que interpola.

SELECT BIT_OR(flags), BIT_AND(flags) FROM permissions;

Agregados bit a bit sobre uma coluna integer: BIT_OR reúne todos os bits ativos, BIT_AND mantém os comuns a todas as linhas. Para máscaras de flags.

SELECT JSONB_AGG(t ORDER BY t.id) FROM tasks t;

Agrupa linhas em um array JSON — útil para retornar dados aninhados em uma única consulta. JSONB_AGG armazena como jsonb (mais rápido, sem chaves duplicadas).

SELECT JSONB_OBJECT_AGG(key, value) FROM settings;

Dobra pares chave-valor em um único objeto JSON. Ideal para transformar uma tabela de configurações em um mapa.

SELECT CORR(price, sales) FROM products;

Coeficiente de correlação de Pearson entre duas colunas: de -1 a 1. Uma medida de associação linear.

SELECT REGR_SLOPE(y, x), REGR_INTERCEPT(y, x) FROM points;

Inclinação e intercepto da reta de regressão de y sobre x — uma tendência em um único agregado, sem pacote estatístico externo.

SELECT REGR_R2(y, x) FROM points;

O coeficiente de determinação R² da regressão de y sobre x: 0..1, quão bem a reta se ajusta aos dados.

SELECT user_id,
  MAX(amount) FILTER (WHERE kind = 'deposit')  AS deposit,
  MAX(amount) FILTER (WHERE kind = 'withdraw') AS withdraw
FROM tx GROUP BY user_id;

Transforma linhas em colunas (pivot): MAX/SUM com FILTER por categoria. Substitui uma pilha de agregados CASE escritos à mão.

SELECT region, product, SUM(amount)
FROM sales
GROUP BY CUBE (region, product);

Todas as combinações de agrupamento de uma vez: por region, por product, por ambos e um total geral. Para relatórios de tabela cruzada.

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

Verifica a pertinência a uma lista gerada por outra consulta.

SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Mantém a linha se a consulta interna encontrar ao menos uma linha correspondente.

SELECT
  name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

Uma subconsulta que retorna um único valor — pode ficar dentro do SELECT.

SELECT id FROM a
UNION ALL
SELECT id FROM b;

Empilha os resultados de duas consultas (mesmo número de colunas, tipos compatíveis). UNION remove duplicatas, UNION ALL as mantém (e é mais rápido).

SELECT user_id FROM purchases
INTERSECT
SELECT user_id FROM refunds;

Linhas presentes em AMBAS as consultas. No MySQL — a partir da 8.0.31.

SELECT user_id FROM users
EXCEPT
SELECT user_id FROM banned;

Linhas da primeira consulta que NÃO estão na segunda. No Oracle é MINUS.

SELECT
  name,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn
FROM players;

Número sequencial único para cada linha dentro da janela.

RANK() OVER (PARTITION BY dept ORDER BY salary DESC)

Classificação com saltos (RANK) ou sem saltos (DENSE_RANK) quando há empates.

SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at)

Divide a janela em grupos — o agregado é calculado por grupo.

LAG(price, 1) OVER (ORDER BY date)

Valor da linha anterior (LAG) ou seguinte (LEAD) da janela.

NTILE(4) OVER (ORDER BY score DESC)

Divide as linhas em N grupos de tamanho igual em ordem. Com quantidades desiguais os grupos ficam 3-3-2-2 (os excedentes vão para os de número menor).

LAST_VALUE(score) OVER (
  PARTITION BY team_id ORDER BY score DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Primeiro / último valor da janela. Para LAST_VALUE é obrigatório ampliar o quadro com ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — caso contrário, o quadro padrão corta a borda direita.

PERCENT_RANK() OVER (ORDER BY score DESC, player_id)

Classificação percentil de 0 a 1. Uma segunda coluna de ordenação torna o resultado determinístico quando há empates.

NTH_VALUE(amount, 2) OVER (
  PARTITION BY customer_id ORDER BY amount DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

O N-ésimo valor da janela. Também requer o quadro ampliado.

AVG(x) OVER (ORDER BY d ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Janelas deslizantes: «os últimos 7 dias inclusive», «média de 3 dias», etc.

WITH active AS (
  SELECT * FROM users WHERE status = 'active'
)
SELECT * FROM active WHERE country = 'RU';

Um resultado temporário nomeado — divide uma consulta grande em etapas.

WITH a AS (...), b AS (...)
SELECT * FROM a JOIN b ON ...;

Vários CTEs separados por vírgulas, lidos de cima para baixo.

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM emp WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id FROM emp e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

Percorre uma hierarquia: consulta de ancoragem → UNION ALL → passo recursivo. Ideal para organogramas, grafos e cadeias.

FROM customers c
LEFT JOIN LATERAL (
  SELECT * FROM orders WHERE customer_id = c.id
  ORDER BY amount DESC LIMIT 2
) l ON true

A subconsulta enxerga as colunas da linha externa. Ideal para «top-N por X». LEFT JOIN LATERAL … ON true mantém as linhas externas sem correspondência; a forma com vírgula as descarta silenciosamente.

generate_series('2024-01-01'::date, '2024-01-15'::date, '1 day')

Gera um calendário / eixo. Inclui ambas as extremidades. Truque padrão para «preencher com zero os dias faltantes».

INSERT INTO t (col1, col2) VALUES (1, 'a'), (2, 'b');

Adiciona linhas a uma tabela.

UPDATE t SET col = 'x' WHERE id = 5;

Modifica linhas existentes. Sempre inclua WHERE — caso contrário, TODAS as linhas são atualizadas.

DELETE FROM t WHERE id = 5;

Exclui linhas. Sempre inclua WHERE.

INSERT INTO t (id) VALUES (1)
ON CONFLICT (id) DO NOTHING;

Inserção idempotente — ao executar novamente, ignora silenciosamente as linhas que já existem.

INSERT INTO t (id, n) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE
  SET n = t.n + EXCLUDED.n;

UPSERT: inserir ou atualizar. EXCLUDED.col é o valor que tentamos inserir.

MERGE INTO t USING src ON t.id = src.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...;

Postgres 15+ — alternativa ao UPSERT com ramos MATCHED / NOT MATCHED e condições em cada um.

INSERT INTO t (name) VALUES ('a')
RETURNING id, name;

Retorna as linhas recém-inseridas / atualizadas / excluídas na mesma instrução — sem uma segunda ida ao servidor.

DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.country = 'US';

DELETE no estilo JOIN — filtra por outra tabela sem uma subconsulta.

UPDATE customers c SET total = s.total
FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) s
WHERE c.id = s.customer_id;

UPDATE em massa impulsionado por uma subconsulta de agregação.

WITH moved AS (
  DELETE FROM orders WHERE old RETURNING *
)
INSERT INTO archive SELECT * FROM moved;

Arquivamento atômico: move as linhas em uma única instrução, sem janela de concorrência entre DELETE e INSERT.

CREATE TABLE t (
  id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Cria uma tabela com colunas tipadas.

ALTER TABLE t ADD COLUMN created_at TIMESTAMP;

Modifica uma tabela existente.

ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

Rejeita valores inválidos no nível do banco de dados, não no código.

FOREIGN KEY (post_id) REFERENCES posts(id)
  ON DELETE CASCADE   -- or SET NULL / RESTRICT

O que fazer com a linha filha quando a linha pai é excluída: propagar em cascata, definir a chave estrangeira como NULL ou bloquear.

ALTER TABLE t
  ADD CONSTRAINT fk REFERENCES p(id) NOT VALID;
ALTER TABLE t VALIDATE CONSTRAINT fk;

Adiciona uma chave estrangeira a uma tabela grande em produção sem um bloqueio pesado: NOT VALID é instantâneo, VALIDATE não bloqueia os escritores.

total NUMERIC GENERATED ALWAYS AS (price * (1 + tax)) STORED

O valor da coluna é calculado automaticamente — a fórmula fica em um único lugar.

CREATE UNIQUE INDEX u ON users (email)
WHERE deleted_at IS NULL;

Unicidade apenas sobre as linhas ativas — para o soft-delete, de modo que os usuários possam se registrar de novo após a exclusão.

CREATE TABLE logs (...) PARTITION BY RANGE (ts);
CREATE TABLE logs_2024 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Divide uma tabela grande por intervalos. As partições antigas são removidas em milissegundos.

CREATE TRIGGER touch BEFORE UPDATE ON notes
FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

Lógica automática no nível do banco de dados — por exemplo, atualizar updated_at sem tocar no código da aplicação.

CREATE MATERIALIZED VIEW v AS SELECT ...;
REFRESH MATERIALIZED VIEW v;

Resultado em cache de uma consulta pesada. É atualizado de forma agendada com REFRESH.

LOWER(name), UPPER(code), LENGTH(text)

Minúsculas, maiúsculas, comprimento da cadeia de caracteres.

CONCAT(first_name, ' ', last_name)

Concatena cadeias de caracteres. O PostgreSQL também aceita o operador || (no MySQL, || é o OU lógico por padrão, não a concatenação).

EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at)

Extrai uma parte de uma data — ano, mês, dia.

DATE_TRUNC('month', created_at)

Trunca um timestamp para baixo até um período (dia/semana/mês). A ferramenta básica para agrupar por tempo.

WHERE created_at >= NOW() - INTERVAL '7 days'

Instante atual (NOW()) / hoje (CURRENT_DATE) e aritmética com intervalos — «nos últimos 7 dias».

CAST(price AS INTEGER)   -- or price::int

Converte um valor para outro tipo. CAST(x AS type) é padrão; x::type é a forma curta do PostgreSQL.

TRIM(name), SUBSTRING(code FROM 1 FOR 3), REPLACE(phone, '-', '')

Remove espaços, extrai uma substring, substitui um fragmento. Limpeza de strings do dia a dia.

SPLIT_PART(email, '@', 2)   -- domain from an e-mail

Divide uma string por um separador e pega a N-ésima parte. No MySQL — SUBSTRING_INDEX.

WHERE name ILIKE '%ivan%'

LIKE sem distinção de maiúsculas (PostgreSQL). No MySQL, o LIKE comum já ignora maiúsculas com a colação padrão.

LEFT(code, 3), RIGHT(phone, 4)

Pega os primeiros N caracteres (LEFT) ou os últimos N (RIGHT) de uma string.

POSITION('@' IN email), STRPOS(email, '@')

Posição da primeira ocorrência (a partir de 1), 0 se não encontrar. STRPOS é a forma curta do PostgreSQL.

LPAD(id::text, 6, '0'), RPAD(name, 20, ' ')

Preenche uma string até um comprimento alvo à esquerda (LPAD) ou à direita (RPAD). Uso clássico: completar um id com zeros.

INITCAP('john DOE')   -- John Doe

Coloca em maiúscula a primeira letra de cada palavra e o resto em minúscula. Não existe no MySQL.

REPEAT('ab', 3)   -- ababab

Repete uma string N vezes. Útil para placeholders e gráficos de barra em texto.

REVERSE(name)

Inverte uma string caractere a caractere. Às vezes usado para indexar por sufixo.

char_length(name), char_length('açai')   -- 4

Comprimento da string em CARACTERES (não bytes) — importa para UTF-8. Sinônimo de character_length.

REGEXP_REPLACE(phone, '[^0-9]', '', 'g')

Substituição por expressão regular. A flag 'g' substitui todas as ocorrências; sem ela, apenas a primeira.

SELECT (REGEXP_MATCHES(url, '/(\d+)'))[1] AS id;

Retorna os grupos capturados da regex como um array. Com a flag 'g' produz uma linha por correspondência.

REGEXP_SPLIT_TO_ARRAY('a, b,c', '\s*,\s*')

Divide uma string por um separador regex em um array. Há também …TO_TABLE para linhas.

TRANSLATE(code, 'abc', 'xyz')   -- a->x, b->y, c->z

Substituição caractere a caractere entre dois conjuntos. Caracteres a mais do primeiro conjunto são removidos. Não é REPLACE.

BTRIM(code, '0'), LTRIM(s), RTRIM(s, '/')

Remove os caracteres indicados de ambas as pontas (BTRIM), da esquerda (LTRIM) ou da direita (RTRIM); espaços por padrão.

FORMAT('Hi %s, id=%L', name, id)

Monta uma string a partir de um template: %s valor, %I identificador, %L literal seguro. Essencial em SQL dinâmico.

WHERE STARTS_WITH(path, '/api/')

Verifica se uma string começa com um prefixo — mais legível que LIKE 'x%'. Disponível desde o PostgreSQL 11.

ascii('A')   -- 65
chr(65)      -- A

Código do primeiro caractere (ascii) e o caractere de um código (chr). No MySQL a inversa se chama CHAR.

to_hex(255)   -- 'ff'

Converte um inteiro em sua string hexadecimal. Útil para cores, máscaras de bits e depuração.

ROUND(3.14159)        -- 3
ROUND(2.5)            -- banker? no: 3

Arredonda para o inteiro mais próximo. As metades arredondam para longe do zero (2.5 → 3).

ROUND(3.14159, 2)     -- 3.14
ROUND(12345.6, -2)    -- 12300

Arredonda para n casas decimais; um n negativo arredonda à esquerda do ponto. Só funciona com numeric, não com float.

CEIL(4.1)   -- 5
CEIL(-4.1)  -- -4

Arredonda para cima até o próximo inteiro. CEILING é um sinônimo.

FLOOR(4.9)   -- 4
FLOOR(-4.1)  -- -5

Arredonda para baixo até o inteiro anterior. Com negativos, afasta-se do zero.

TRUNC(3.99)     -- 3
TRUNC(3.456, 2) -- 3.45

Descarta a parte fracionária (em direção ao zero), sem arredondar. No MySQL é TRUNCATE(x, n).

ABS(-7)   -- 7

Valor absoluto — a magnitude sem sinal.

MOD(10, 3)   -- 1

Resto da divisão. Útil para «cada N-ésima linha» e paridade; o sinal do resultado segue o dividendo.

POWER(2, 10)   -- 1024

Eleva a uma potência. POW é um sinônimo.

SQRT(144)   -- 12

Raiz quadrada. Um argumento negativo gera um erro.

EXP(1)    -- 2.7182818...
LN(2.718) -- ~1

Exponencial e^x e logaritmo natural (base e). LN(0) e LN(negativo) geram erro.

LOG(100)     -- 2  (base 10)
LOG(2, 8)    -- 3  (base 2)

No PostgreSQL LOG(x) é base 10, LOG(b, x) usa uma base arbitrária. Atenção: no MySQL LOG(x) é o logaritmo natural.

SIGN(-42)  -- -1
SIGN(0)    -- 0

Sinal de um número: -1, 0 ou 1. Útil para ramificar conforme a direção da mudança.

GREATEST(a, b, c), LEAST(a, b, c)

O maior / menor entre os argumentos dentro de uma linha (não é agregação). Argumentos NULL são ignorados.

SELECT * FROM t ORDER BY RANDOM() LIMIT 5;

Número aleatório em [0,1). No MySQL é RAND(). ORDER BY RANDOM() dá uma amostra aleatória, mas é caro em tabelas grandes.

DIV(7, 2)   -- 3
7 / 2       -- 3 when both are int

Divisão inteira que descarta o resto. No PostgreSQL / já divide inteiro quando ambos os operandos são inteiros; o MySQL usa o operador DIV para isso.

WIDTH_BUCKET(score, 0, 100, 10)  -- bucket 1..10

Atribui um valor a um balde de histograma de largura igual entre dois limites. Para distribuições e agrupamento por faixas.

AGE(end_ts, start_ts)   -- or AGE(birthday) vs now
AGE('2024-03-01', '2024-01-15')

Diferença entre duas datas como intervalo (anos/meses/dias), não em segundos. Com um único argumento conta a partir de hoje — útil para a idade.

DATE_PART('hour', created_at), DATE_PART('dow', created_at)

Forma de função do EXTRACT — extrai uma parte de data/hora como número. O campo é uma string, então é fácil passá-lo dinamicamente.

EXTRACT(EPOCH FROM (ended_at - started_at)) AS seconds

Converte um intervalo ou timestamp em segundos (tempo Unix). A forma padrão de medir uma duração em segundos — depois divida por 60/3600.

TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI'), TO_CHAR(amount, 'FM999G999D00')

Formata uma data/número em uma string via um padrão (YYYY, MM, DD, HH24...). Os padrões do PostgreSQL diferem do DATE_FORMAT do MySQL.

TO_DATE('2024-03-15', 'YYYY-MM-DD')

Converte uma string em uma data com um padrão explícito. Mais confiável que o cast ::date quando o formato não é padrão.

TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI')
TO_TIMESTAMP(1710512400)   -- from Unix epoch

Converte uma string em um timestamp por padrão, ou constrói um timestamptz a partir de segundos Unix (argumento numérico).

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP;

Hora de início da transação: CURRENT_TIMESTAMP inclui fuso horário (timestamptz), LOCALTIMESTAMP não. Constante dentro de uma mesma transação.

SELECT CURRENT_DATE, CURRENT_TIME;

Apenas a data de hoje (CURRENT_DATE) ou apenas a hora (CURRENT_TIME) — sem parênteses; são valores especiais do SQL, não funções.

SELECT order_date + 7, due_date - 1, end_dt - start_dt AS days;

Você pode somar/subtrair dias inteiros de um date (date + 7). Subtrair dois date dá um número inteiro de dias; dois timestamp dão um intervalo.

make_date(2024, 3, 15), make_time(14, 30, 0)

Constrói uma data ou hora a partir de números separados de ano/mês/dia, hora/minuto/segundo — sem lidar com strings de formato.

make_timestamp(2024, 3, 15, 14, 30, 0)
make_interval(days => 10, hours => 2)

Constrói um timestamp ou intervalo a partir de componentes numéricos. make_interval aceita argumentos nomeados (days =>, hours =>).

ts_utc AT TIME ZONE 'Europe/Moscow'
local_ts AT TIME ZONE 'UTC'

Leva um instante para outro fuso horário. Sobre um timestamptz dá a hora local desse fuso; sobre um timestamp sem fuso interpreta-o como hora desse fuso.

JUSTIFY_HOURS(INTERVAL '36 hours')   -- 1 day 12:00:00

Normaliza um intervalo: converte as horas excedentes em dias e os dias em meses. Transforma «50 hours» em um legível «2 days 02:00:00».

DATE_BIN('15 minutes', ts, TIMESTAMP '2024-01-01')

Ajusta um timestamp para o início de um bucket de largura arbitrária (ex.: 15 minutos) a partir de uma origem. Mais flexível que DATE_TRUNC. Postgres 14+.

(start_a, end_a) OVERLAPS (start_b, end_b)

Verifica se dois períodos de tempo se sobrepõem. Útil para encontrar conflitos de reservas ou turnos.

now()::timestamptz, '2024-03-15 10:00'::timestamp

timestamptz guarda o instante em UTC e aplica o fuso na exibição; timestamp é hora «ingênua» sem fuso. Para eventos quase sempre você quer timestamptz.

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 70 THEN 'B'
  ELSE 'C'
END

Lógica condicional embutida — como um if/else dentro do SELECT.

COALESCE(nickname, full_name, 'Anonymous')

Retorna o primeiro valor não NULL da lista.

NULLIF(divisor, 0)

Transforma um valor em NULL quando ele é igual ao segundo argumento. Útil para evitar a divisão por zero.

-- = NULL is never true — use IS NULL
WHERE deleted_at IS NULL
-- NULL-safe equality:
WHERE a IS DISTINCT FROM b

Comparar com NULL usando = sempre dá «desconhecido» (não TRUE/FALSE). Use IS NULL para testar; use IS DISTINCT FROM para igualdade segura com NULL.

payload->>'target'

Extrai um valor de um JSONB como text — para operações com cadeias e comparações.

payload @> '{"plan":"pro"}'

O JSONB contém o fragmento indicado? Usa um índice GIN — rápido em tabelas grandes.

CREATE INDEX idx ON events USING GIN (payload jsonb_path_ops)

Índice ideal para consultas @> sobre JSONB. Mais compacto que o jsonb_ops padrão.

data->'user'->>'name'   -- -> keeps json, ->> as text

-> extrai um campo/elemento como jsonb (para continuar navegando), ->> como text. Chave por string, índice de array por número.

data #>> '{address,city}'   -- text at a nested path

Lê um valor em um caminho aninhado dado como array de chaves: #> como jsonb, #>> como text. Mais curto que encadear ->.

jsonb_build_object('id', id, 'name', name)

Constrói um objeto JSON a partir de pares chave, valor alternados. Os tipos dos valores são preservados (números continuam números).

jsonb_build_array(id, name, created_at)

Constrói um array JSON a partir dos argumentos dados de quaisquer tipos.

jsonb_agg(item ORDER BY created_at)

Agregado: reúne um grupo de linhas em um array JSON. O ORDER BY interno fixa a ordem dos elementos.

SELECT e FROM t, jsonb_array_elements(t.tags) AS e

Expande um array JSON em linhas — uma linha por elemento. A variante _text retorna text em vez de jsonb.

jsonb_array_length(data->'items')

Comprimento de um array JSON. Gera erro se o valor não for um array — proteja com jsonb_typeof.

jsonb_set(data, '{address,city}', '"Lima"')

Retorna uma cópia do JSON com o valor do caminho substituído. create_missing=true (padrão) adiciona a chave se ela faltar.

SELECT key, value FROM jsonb_each(data)

Expande um objeto JSON em linhas (key, value) — uma por chave. A variante _text retorna value como text.

SELECT jsonb_object_keys(data)

Retorna os nomes das chaves de nível superior de um objeto JSON, uma linha por chave.

data ? 'email'        -- has key?
data ?| array['a','b'] -- any of these keys?

Testes de existência de chaves: ? uma chave, ?| qualquer destas, ?& todas. Apenas nível superior; suportam índice GIN.

data || '{"verified":true}'

Mescla dois valores JSONB: as chaves da direita sobrescrevem as da esquerda (superficial, não recursivo). Útil para uma atualização parcial.

data - 'temp'              -- drop a key
data #- '{address,zip}'    -- drop at a path

Remove uma chave/elemento: - por chave ou índice de nível superior, #- em um caminho aninhado. Retorna um novo JSONB.

to_jsonb(row_var)   -- whole row as a json object

Converte qualquer valor/linha/array de SQL em jsonb. Uma linha inteira da tabela vira um objeto JSON coluna → valor.

jsonb_typeof(data->'price')   -- 'number','string',...

O tipo do valor JSON como text: object, array, string, number, boolean, null. Útil antes de jsonb_array_length etc.

jsonb_pretty(data)

Formata JSONB com indentação para uma saída legível ou depuração.

CREATE INDEX i ON orders (id) WHERE status = 'pending';

Índice apenas sobre o subconjunto «quente» de linhas — mais compacto e mais rápido de varrer.

CREATE INDEX i ON orders (customer_id, created_at DESC);

Cobre o filtro e a ordenação em uma única leitura. A ordem das colunas importa.

-- bad : WHERE EXTRACT(YEAR FROM ts) = 2024
-- good: WHERE ts >= '2024-01-01' AND ts < '2025-01-01'

Não envolva a coluna em uma função — o índice não será usado. Reescreva como um intervalo.

-- NOT IN collapses to 0 rows on any NULL
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NOT IN quebra silenciosamente diante de qualquer NULL na subconsulta. NOT EXISTS é seguro com NULL.

CREATE INDEX CONCURRENTLY i ON events (user_id, kind);

Constrói um índice em uma tabela quente sem um bloqueio pesado. É proibido dentro de uma transação.

EXPLAIN SELECT * FROM orders WHERE user_id = 5;

Mostra o plano da consulta sem executá-la: quais varreduras (Seq Scan / Index Scan), ordem dos joins, linhas estimadas.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 5;

Executa a consulta e mostra o tempo e as linhas REAIS versus a estimativa. Uma grande diferença estimativa↔real indica estatísticas desatualizadas ou um índice ausente.

BEGIN;
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
COMMIT;

Bloqueia linhas até o fim da transação. Padrão para transferências de dinheiro.

UPDATE accounts SET balance = balance - 200
WHERE id = 1 AND balance >= 200;

Verificação e atualização em uma única instrução atômica. Se 0 linhas forem atualizadas — exiba «saldo insuficiente».

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY id
FOR UPDATE SKIP LOCKED LIMIT 1;

Fila de workers: cada worker pega a sua própria tarefa, ignorando as linhas bloqueadas por outros.

UPDATE counters SET n = n + 1 WHERE id = 1;

Um único UPDATE incrementa o contador de forma segura diante da concorrência. SELECT seguido de UPDATE perde incrementos.

GRANT SELECT, INSERT ON orders TO analyst;

Concede privilégios sobre um objeto a um papel/usuário. Liste as ações necessárias (SELECT, INSERT, UPDATE, DELETE).

REVOKE INSERT ON orders FROM analyst;

Revoga privilégios concedidos anteriormente.

CREATE ROLE analyst LOGIN PASSWORD 'secret';

Cria um papel (usuário/grupo). Os privilégios são concedidos ao papel, e os usuários são membros dele.

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

O padrão clássico somente-leitura: acesso ao esquema + SELECT em todas as tabelas + uma regra para tabelas futuras via ALTER DEFAULT PRIVILEGES.