PostgreSQL COALESCE Function: Um Guia Abrangente

No ambiente orientado a dados de hoje, o PostgreSQL continua a ser uma escolha líder para gerenciamento robusto de banco de dados. Entre seu arsenal de recursos, a função PostgreSQL COALESCE se destaca como uma poderosa ferramenta para gerenciar valores nulos. Como um profissional de banco de dados, entender e aproveitar a função COALESCE é crucial para a manipulação eficaz de dados e a obtenção de resultados de consulta precisos.

Embora o PostgreSQL não tenha uma função ISNULL interna como o SQL Server, isso não significa que o PostgreSQL não tenha a capacidade de manipular valores nulos de forma eficaz. Muito pelo contrário, o PostgreSQL nos deu a versátil função COALESCE e operadores como IS NULL ou IS NOT NULL, que trabalham de forma impressionante para gerenciar valores nulos, muitas vezes superando seus equivalentes em outros sistemas SQL.

Neste guia vamos nos aprofundar na função COALESCE do PostgreSQL, explorando sua sintaxe, asos de uso e aplicações práticas. Também veremos como ele pode ser usado como uma alternativa à função ISNULL encontrada em outros ambientes SQL.

Não há nenhuma função ISNULL do PostgreSQL

Entendendo o Null no PostgreSQL

Em qualquer banco de dados, null significa a ausência de um valor. Não é zero, não é uma cadeia de caracteres vazia - é um marcador distinto que representa dados ausentes ou indefinidos. No entanto, lidar com esses nulos pode ser complicado e levar a resultados inesperados se não for gerenciado cuidadosamente. É aqui que a função COALESCE do PostgreSQL pode vir em nosso socorro.

É importante notar que, na data em que este post foi escrito, o PostgreSQL não tem uma função ISNULL interna, ao contrário do SQL Server. No entanto, o PostgreSQL nos fornece a função COALESCE e os operadores IS NULL ou IS NOT NULL que podem ser usados para obter resultados semelhantes. Então, para o bem deste post, quando discutimos ISNULL, estamos nos referindo a essas alternativas no PostgreSQL.

PostgreSQL COLAESCE Sintaxe e Funcionalidade

A função COALESCE no PostgreSQL retorna o primeiro argumento não nulo. É efetivamente equivalente à função ISNULL no SQL Server. A sintaxe para COALESCE é a seguinte:

COALESCE(value1, value2, ..., valueN)

Por outro lado, os operadores IS NULL ou IS NOT NULL testam se um valor é nulo ou não, respectivamente.

Agora vamos ver alguns exemplos.

Exemplo 1: Uso básico do COALESCE

Suponha que tenhamos uma tabela de produtos da seguinte maneira:

Se quisermos substituir todos os SupplierIDs nulos por um valor padrão de 0, podemos usar COALESCE da seguinte maneira:

SELECT ProductID, ProductName, COALESCE(SupplierID, 0) AS SupplierID
FROM Products;

Essa consulta retornará o seguinte:

Exemplo 2: Usando `` o operador

IS NULLO operador IS NULL pode ser usado em uma cláusula WHERE para filtrar valores nulos. Por exemplo, se quiséssemos encontrar todos os produtos sem um fornecedor, poderíamos usar:

SELECT *
FROM Products
WHERE SupplierID IS NULL;

Isso retornaria os produtos com SupplierID nulo:

Exemplo 3: Combinando COALESCE com outras funções

COALESCE pode ser combinado com outras funções para consultas mais complexas. Por exemplo, considere a seguinte tabela de pedidos:

Desculpas pelo corte abrupto. Vamos continuar de onde paramos.

Exemplo 4: Combinando COALESCE com outras funções (continuação)

Se quiséssemos calcular o preço total de cada pedido, poderíamos multiplicar Quantidade e Preço. No entanto, isso pode levar a resultados inesperados quando valores nulos estão presentes. Qualquer operação aritmética com um valor nulo resulta em um nulo. Então, vamos usar o COALESCE para lidar com isso:

SELECT OrderID, COALESCE(Quantity, 0) * COALESCE(Price, 0) AS TotalPrice
FROM Orders;

Isso substituirá quaisquer valores null por 0 antes de realizar a multiplicação, dando-nos:

Exemplo 5: Usando o operador IS NOT NULL

Em contraste com o operador IS NULL, o operador IS NOT NULL filtra valores não nulos. Por exemplo, se quiséssemos encontrar todos os pedidos com Quantidade e Preço definidos, poderíamos usar:

SELECT *
FROM Orders
WHERE Quantity IS NOT NULL AND Price IS NOT NULL;

Isso retornaria os pedidos com Quantidade e Preço não nulos:

Exemplo 6: Usando COALESCE com CASE WHEN

A função COALESCE também pode ser usada em conjunto com CASE WHEN para criar regras com mais nuances para manipular valores nulos. A cláusula CASE WHEN nos permite executar lógica condicional em SQL. Combinado com COALESCE, isso pode nos permitir atribuir diferentes valores padrão com base em determinadas condições.

Considere uma versão estendida da tabela Produtos anterior:

Digamos que queremos substituir SupplierID s nulo, mas o valor padrão deve ser 100 para Categoria 1 e 200 para Categoria 2. Podemos conseguir isso com COALESCE e CASE WHEN assim:

SELECT 
    ProductID, 
    ProductName, 
    CASE 
        WHEN CategoryID = 1 THEN COALESCE(SupplierID, 100)
        WHEN CategoryID = 2 THEN COALESCE(SupplierID, 200)
        ELSE SupplierID
    END AS SupplierID,
    CategoryID
FROM Products;

Essa consulta substitui SupplierID nulo por 100 para produtos na Categoria 1 e por 200 para produtos na Categoria 2, fornecendo um valor padrão condicional. Se o CategoryID de um produto não for nem 1 nem 2, o SupplierID permanecerá como está.

O resultado retornado será:

Essa combinação de COALESCE com CASE WHEN demonstra a flexibilidade e o poder que o PostgreSQL fornece ao trabalhar com valores nulos.

Exemplo 7: Usando COALESCE com CASE WHEN em queries complexas

A função COALESCE combinada com a instrução CASE WHEN é uma ferramenta poderosa para gerenciar dados, incluindo operações de agrupamento e soma em consultas complexas. Neste exemplo, lidamos com uma situação comum em que valores de cadeia de caracteres nulos ou vazios estão presentes em uma coluna de texto categórica e queremos que eles sejam tratados como uma categoria específica.

Considere uma tabela mytable com uma coluna de texto mycat, que normalmente contém valores que variam de ‘0’ a ‘4’, e três colunas numéricas adicionais col1, col2, col3. Devido a um erro no processo de entrada de dados, há valores de cadeia de caracteres nulos ou vazios ocasionais no mycat qu` devem ser tratados da mesma forma que ‘0’.

Suponha que tenhamos os seguintes dados em mytable:

Veja como lidar com essa situação usando COALESCE com CASE WHEN em uma consulta PostgreSQL:

SELECT my_category, 
       COALESCE(SUM(col1), 0), 
       COALESCE(SUM(col2), 0), 
       COALESCE(SUM(col3), 0) 
FROM
(
SELECT CASE COALESCE(mycat ,'0')                                     
       WHEN '0' THEN 'ZERO'                         
       WHEN '1' THEN 'ONE'                      
       WHEN '2' THEN 'TWO'                      
       WHEN '3' THEN 'THREE'                        
       WHEN '4' THEN 'OTHER'        
       ELSE 'ZERO'      
   END AS my_category,
   col1,
   col2,
   col3
   FROM mytable 
) T
GROUP BY my_category
ORDER BY my_category;

Nesta consulta, COALESCE(mycat ,'0') substitui valores de cadeia de caracteres nulos ou vazios em mycat por ‘0’. A declaração CASE WHEN então renomeia essas categorias para ‘ZERO’, ‘ONE’, ‘TWO’, ‘THREE’ e ‘OTHER’. As categorias resultantes são então usadas para agrupar os valores somados de col1``, col2 e col3.

A instrução SELECT externa aplica COALESCE a essas somas para garantir que, se uma categoria não tiver valores associados em col1, col2 ou col3, um valor zero será retornado em vez de nulo.

O resultado da consulta será agrupado pelas novas categorias, somando os valores de col1, col2 e col3 para cada categoria e garantindo que a categoria de cadeia de caracteres nula ou vazia seja tratada como ‘0’, somando-a e agrupando-a com a categoria ‘ZERO’.

Se executarmos a consulta fornecida, aqui está o que devemos esperar:

Esse resultado reflete que os valores ’’ e NULL em mycat foram tratados como 0, de modo que seus valores correspondentes de col1, col2 e col3 são somados com os de 0 na categoria ZERO. As demais categorias (UM, DOIS, TRÊS, OUTROS) são somadas da mesma forma.

Este exemplo demonstra como manipular valores de cadeia de caracteres nulos ou vazios em dados categóricos no PostgreSQL usando COALESCEeCASE WHEN`. Ele mostra a utilidade dessas funções na limpeza e gerenciamento de dados, particularmente em operações complexas de agrupamento e somatório.

Conclusão

Embora o PostgreSQL não forneça uma função ISNULL exata como o SQL Server, seu conjunto robusto de alternativas (COALESCE, IS NULL, IS NOT NULL) não são menos poderosos no gerenciamento e manipulação de valores nulos. Lembre-se, compreender e lidar efetivamente com valores nulos é um aspecto crucial do gerenciamento de banco de dados e pode melhorar significativamente a confiabilidade e a precisão de suas operações de dados.