PostgreSQL: Filtragem Avançada e Correspondência de Padrões…

Última atualização: 2026-08-26

1. O Que Você Vai Aprender

• Combinar múltiplas condições de filtro com AND/OR/NOT
• Filtragem de intervalo e NULL com IN/BETWEEN/IS NULL
• Correspondência de padrões com LIKE/ILIKE (recurso de insensibilidade a maiúsculas do PostgreSQL)
• SIMILAR TO e operadores regex ~ / ~* / !~ / !~*
• Filtragem com subconsultas usando ANY/ALL/EXISTS

2. A História

Alice é uma desenvolvedora de recursos de busca em uma plataforma de e-commerce. Quando um usuário digita uma palavra-chave na caixa de busca, o sistema precisa:

1. Fazer correspondência difusa com nomes e descrições de produtos
2. Suportar busca sem distinção de maiúsculas/minúsculas
3. Permitir que usuários avançados usem expressões regulares para busca precisa
4. Filtrar produtos descontinuados
5. Classificar resultados por relevância

Alice precisa dominar as várias ferramentas de filtragem e correspondência de padrões que o PostgreSQL oferece para construir este sistema de busca.

3. Conceito: Combinações AND / OR / NOT

(1) Precedência de Operadores Lógicos

▶ Exemplo: Combinação AND

SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
  AND unit_price > 500
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Combinação OR

SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
   OR category = 'Books'
   OR category = 'Toys';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Controlando a Lógica com Parênteses

OR tem precedência menor que AND; omitir parênteses pode gerar resultados inesperados.

▶ Exemplo: Parênteses Mudam a Lógica

SELECT product_name, unit_price, category
FROM products
WHERE is_active = true
  AND (category = 'Electronics' OR category = 'Books');

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Negação com NOT

NOT nega qualquer expressão booleana e é frequentemente combinado com IN, BETWEEN, LIKE, etc.

▶ Exemplo: NOT Nega uma Condição

SELECT product_name, unit_price
FROM products
WHERE NOT category = 'Electronics'
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

4. Conceito: IN / BETWEEN / IS NULL

(1) IN e NOT IN

IN verifica se um valor está em uma determinada lista—equivalente a múltiplos OR, mas mais conciso e com melhor desempenho.

▶ Exemplo: Filtro IN para Múltiplas Categorias

SELECT product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Books', 'Toys')
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Filtro de Intervalo BETWEEN

BETWEEN é inclusivo em seus limites, equivalente a >= AND <=.

▶ Exemplo: Filtro de Faixa de Preço

SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 100 AND 500
ORDER BY unit_price;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Filtro de Intervalo de Data

SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30'
  AND order_status = 'completed';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) IS NULL / IS NOT NULL

NULL não é igual a nada (nem a si mesmo); você deve usar IS NULL ou IS NOT NULL para testá-lo.

▶ Exemplo: Encontrar Produtos sem Desconto

SELECT product_name, unit_price, discount_rate
FROM products
WHERE discount_rate IS NULL
  AND is_active = true;

Output:

 count 
-------
     5
(1 row)

(4) A Armadilha do NOT IN e NULL

Quando a lista NOT IN contém NULL, toda a expressão pode retornar um conjunto de resultados vazio.

▶ Exemplo: Usando NOT IN com Segurança

-- Perigoso: NULL na subconsulta pode quebrar NOT IN
SELECT product_name
FROM products
WHERE category NOT IN (
  SELECT category FROM categories WHERE is_active = false
);

-- Seguro: use NOT EXISTS em vez disso
SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM categories c
  WHERE c.category = p.category AND c.is_active = false
);

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Conceito: Correspondência de Padrões com LIKE / ILIKE

(1) Caracteres Curinga do LIKE

▶ Exemplo: Correspondência de Prefixo com LIKE

SELECT product_name, unit_price
FROM products
WHERE product_name LIKE 'Wireless%'
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Correspondência de Conteúdo com LIKE

SELECT product_name, unit_price
FROM products
WHERE product_name LIKE '%Battery%'
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) ILIKE: Insensível a Maiúsculas (Recurso do PostgreSQL)

ILIKE é uma extensão do PostgreSQL que faz correspondência sem distinção de maiúsculas/minúsculas, equivalente a LIKE + LOWER().

▶ Exemplo: Busca com ILIKE

SELECT product_name, unit_price, description
FROM products
WHERE (product_name ILIKE '%iphone%'
   OR description ILIKE '%iphone%')
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Escapando Caracteres Curinga

Quando você precisa buscar um % ou _ literal, use ESCAPE para especificar um caractere de escape.

▶ Exemplo: Buscar Descrições Contendo Símbolo de Porcentagem

SELECT product_name, description
FROM products
WHERE description LIKE '%100\%%' ESCAPE '\';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. Conceito: Correspondência com Expressões Regulares

(1) Visão Geral dos Operadores Regex

O PostgreSQL fornece quatro operadores de correspondência regex, mais a sintaxe SIMILAR TO.

▶ Exemplo: Correspondência Regex com ~

SELECT product_name, unit_price
FROM products
WHERE product_name ~ '^(Wireless|Bluetooth)'
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: Regex Insensível a Maiúsculas com ~*

SELECT product_name, unit_price
FROM products
WHERE product_name ~* 'iphone\s*(1[0-9])?'
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) SIMILAR TO

SIMILAR TO fica entre LIKE e regex POSIX: suporta |, [], *, +, etc., mas usa os curingas estilo SQL % e _.

▶ Exemplo: Correspondência com SIMILAR TO

SELECT product_name, unit_price
FROM products
WHERE product_name SIMILAR TO '(Wireless|Bluetooth)%'
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: !~ Exclui uma Correspondência Regex

SELECT product_name, unit_price
FROM products
WHERE product_name !~ '(Refurbished|Used)'
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

7. Conceito: Filtragem com Subconsultas ANY / ALL / EXISTS

(1) ANY e ALL

▶ Exemplo: ANY Equivalente a IN

SELECT product_name, unit_price
FROM products
WHERE category = ANY(ARRAY['Electronics', 'Books', 'Toys'])
  AND is_active = true;

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Exemplo: ALL Comparado a uma Subconsulta

SELECT product_name, unit_price, category
FROM products p
WHERE unit_price > ALL (
  SELECT unit_price FROM products
  WHERE category = 'Books' AND is_active = true
);

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Subconsulta EXISTS

EXISTS verifica se a subconsulta retorna alguma linha; não se importa com os valores reais, apenas se existem resultados. É mais seguro que IN (sem armadilha de NULL) e geralmente mais rápido para subconsultas correlacionadas.

▶ Exemplo: EXISTS Encontra Clientes com Pedidos

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Guia de Escolha entre IN e EXISTS

8. Fluxograma: Escolha de Correspondência de Padrões

flowchart TD
    A[Precisa de correspondência de padrões?] --> B{Sensível a maiúsculas?}
    B -->|Sensível| C{Precisa de regex?}
    B -->|Insensível| D{Precisa de regex?}
    C -->|Curinga simples| E[LIKE]
    C -->|Precisa de OR/classe de caracteres| F[SIMILAR TO]
    C -->|Regex completo| G["operador ~"]
    D -->|Curinga simples| H[ILIKE]
    D -->|Regex completo| I["operador ~*"]
    E --> J[Retornar resultado]
    F --> J
    G --> J
    H --> J
    I --> J

    style A fill:#e1f5fe
    style J fill:#c8e6c9

flowchart TD
    A[Precisa de correspondência de padrões?] --> B{Sensível a maiúsculas?}
    B -->|Sensível| C{Precisa de regex?}
    B -->|Insensível| D{Precisa de regex?}
    C -->|Curinga simples| E[LIKE]
    C -->|Precisa de OR/classe de caracteres| F[SIMILAR TO]
    C -->|Regex completo| G["operador ~"]
    D -->|Curinga simples| H[ILIKE]
    D -->|Regex completo| I["operador ~*"]
    E --> J[Retornar resultado]
    F --> J
    G --> J
    H --> J
    I --> J

    style A fill:#e1f5fe
    style J fill:#c8e6c9

9. Exemplo Completo

Alice implementa a lógica central de consulta para o recurso de busca do e-commerce.

-- Passo 1: Busca básica por palavra-chave com ILIKE (insensível a maiúsculas)
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
  AND (product_name ILIKE '%keyboard%' OR description ILIKE '%keyboard%')
ORDER BY unit_price ASC;

-- Passo 2: Busca avançada com regex para usuários avançados
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
  AND product_name ~* '(mechanical|wireless)\s*keyboard'
  AND unit_price BETWEEN 50 AND 300
ORDER BY unit_price DESC;

-- Passo 3: Buscar produtos em categorias específicas com tratamento de NULL
SELECT product_id, product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Computer Accessories', 'Gaming')
  AND is_active = true
  AND discount_rate IS NOT NULL
  AND unit_price BETWEEN 20 AND 500
ORDER BY unit_price DESC NULLS LAST;

-- Passo 4: Produtos que têm pelo menos um pedido concluído (EXISTS)
SELECT p.product_id, p.product_name, p.unit_price
FROM products p
WHERE p.is_active = true
  AND EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
    WHERE oi.product_id = p.product_id
      AND o.order_status = 'completed'
      AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
  )
  AND NOT EXISTS (
    SELECT 1 FROM product_flags pf
    WHERE pf.product_id = p.product_id
      AND pf.flag_type = 'recalled'
  )
ORDER BY p.unit_price DESC
FETCH FIRST 50 ROWS ONLY;

❓ Perguntas Frequentes

P: A diferença de desempenho entre LIKE e ILIKE é significativa? R: Como ILIKE deve ignorar maiúsculas, ele não pode usar um índice B-tree padrão. Em grandes volumes de dados, use a extensão pg_trgm do PostgreSQL para criar um índice GIN que acelera consultas ILIKE.

P: E se uma subconsulta NOT IN retornar NULL? R: Se o resultado da subconsulta contiver NULL, NOT IN como um todo pode retornar um resultado vazio. Soluções: 1) substitua por NOT EXISTS; 2) adicione WHERE col IS NOT NULL na subconsulta para filtrar NULLs.

P: BETWEEN pode ser usado em TIMESTAMP? R: Sim, mas note que BETWEEN é inclusivo. Para um TIMESTAMP, BETWEEN '2025-01-01' AND '2025-01-31' exclui a porção de tempo de 31 de janeiro. Prefira >= AND < em vez disso.

P: Qual é melhor, SIMILAR TO ou regex POSIX? R: SIMILAR TO é um subconjunto padrão SQL—portável, mas limitado em recursos. Regex POSIX (operador ~) é completo em recursos e é a escolha recomendada em projetos PostgreSQL.

P: Qual a diferença entre ANY e IN? R: col = ANY(array) é funcionalmente equivalente a col IN (lista), mas ANY aceita um argumento array e arrays retornados por subconsultas, e suporta comparações incomuns como > ANY e < ALL; IN suporta apenas igualdade.

P: SELECT 1 ou SELECT * em uma subconsulta EXISTS? R: Funcionalmente idêntico—o otimizador ignora a lista SELECT. SELECT 1 é a forma tradicional e ligeiramente mais concisa; SELECT * também funciona, sem diferença de desempenho.

P: Por que LIKE '%palavra-chave%' não pode usar um índice? R: O curinga inicial '%palavra-chave' impede o índice B-tree porque a ordem de classificação do índice não pode ser aproveitada. Soluções: índice GIN pg_trgm, busca de texto completo (tsvector + tsquery) ou um mecanismo de busca dedicado.

P: Qual sintaxe regex os operadores regex suportam? *R: O PostgreSQL usa Expressões Regulares Estendidas POSIX (ERE), suportando quantificadores +?{m,n}, classes de caracteres [], agrupamento (), alternação | e limites \b, etc. NÃO suporta \d \w \s estilo Perl (use [0-9] [a-zA-Z0-9_] [[:space:]] em vez disso).

📖 Resumo

• Cuidado com a precedência de operadores em AND/OR/NOT; use parênteses para controlar a lógica explicitamente
• IN serve para igualdade com múltiplos valores; BETWEEN para filtros de intervalo; IS NULL para valores ausentes
• LIKE faz correspondência simples com curingas; ILIKE é insensível a maiúsculas (exclusivo do PostgreSQL)
• Regex POSIX ~ / ~* fornece poder total de regex; SIMILAR TO fica entre LIKE e regex
• EXISTS é mais seguro que IN (sem armadilha de NULL) e geralmente mais rápido para subconsultas correlacionadas
• ANY/ALL fornecem comparações quantificadas sobre arrays / subconsultas

📝 Exercícios

1. ⭐ Escreva uma consulta que encontre produtos na tabela products onde category é Electronics ou Books e is_active = true, ordenados por unit_price decrescente.

2. ⭐⭐ Escreva uma consulta que use ILIKE para buscar no product_name e description por uma palavra-chave do usuário (ex.: "wireless mouse"), filtrando unit_price BETWEEN 20 AND 200 e excluindo produtos cujo discount_rate IS NULL.

3. ⭐⭐⭐ Escreva uma consulta que use EXISTS para encontrar clientes que fizeram um pedido nos últimos 90 dias, e use NOT EXISTS para excluir clientes marcados como "suspensos", ordenados por tempo de registro do cliente decrescente, mostrando as primeiras 20 linhas via paginação.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós