quarta-feira, 4 de setembro de 2013

Você REALMENTE Sabe Lidar com Valores Nulos no PostgreSQL?

O título desta postagem é uma pergunta que me fiz. Eu achava que sim, mas ao fazer uns testes e pesquisas, vi que o assunto é mais amplo do que eu pensava inicialmente. Convido o leitor a explorar todas as principais variações  do tratamento de valores nulos no postgresql.


* Primeiro ponto: Nulo não é zero, nem '', nem espaço.

Nulo significa nulo, que o valor não foi preenchido. Vejamos o exemplo abaixo:

teste=# SELECT (null = '') IS TRUE AS COMPARA1, (null = ' ') IS TRUE AS COMPARA2, (null = 0) IS TRUE AS COMPARA3;
 compara1 | compara2 | compara3
----------+----------+----------
 f        | f        | f
(1 registro)


* Saiba testar se um valor é nulo ou não (IN NULL e IS NOT NULL)

O exemplo abaixo utiliza IS NULL e IS NOT NULL:

teste=# SELECT (1 IS NULL) AS COMPARA1, (1 IS NOT NULL) AS COMPARA2;
 compara1 | compara2
----------+----------
 f        | t
(1 registro)


* Saiba que valores nulos retornam nulo em comparações (IN, NOT IN, =, !=, etc.)

Esta é uma decorrência do padrão SQL.

teste=# SELECT null NOT IN (1,2,3), null IN (1,2,3);
 ?column? | ?column?
----------+----------
          |
(1 registro)

teste=# SELECT 1 NOT IN (null), 1 IN (null);
 ?column? | ?column?
----------+----------
          |
(1 registro)

teste=# SELECT 1 != null, 1 = null;
 ?column? | ?column?
----------+----------
          |
(1 registro)


* Criar tabelas que aceitem e rejeitem campos nulos é importante!

teste=# CREATE TABLE TSTNULL (campo1 INTEGER NOT NULL, campo2 INTEGER NULL, campo3 INTEGER);
CREATE TABLE


* Como manipular valores nulos e não nulos nas tabelas criadas?

Use o valor null para deixar o campo desejado sem valor algum. Caso se utilize DEFAULT, o campo assume valor nulo se não há um valor default definido.

teste=# INSERT INTO TSTNULL VALUES (1, null, DEFAULT); --O DEFAULT é NULO, CASO NÃO FORNECIDO
INSERT 0 1
teste=# INSERT INTO TSTNULL VALUES (null, 1, 1);
ERRO:  valor nulo na coluna "campo1" viola a restrição não-nula
 
teste=#
teste=# SELECT * FROM TSTNULL;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)


* Entenda que campos UNIQUE aceitam vários valores nulos.

teste=# CREATE TABLE TSTNULL2 (campo1 INTEGER UNIQUE);
NOTA:  CREATE TABLE / UNIQUE criará índice implícito "tstnull2_campo1_key" na tabela "tstnull2"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL2 VALUES (1);
INSERT 0 1
teste=# INSERT INTO TSTNULL2 VALUES (NULL);
INSERT 0 1
teste=# INSERT INTO TSTNULL2 VALUES (NULL); --NAO GERA ERRO
INSERT 0 1
teste=#
teste=# SELECT * FROM TSTNULL2;
 campo1
--------
      1
      
      
(3 registros)


* Campos PRIMARY KEY não aceitam nulos por definição. Você sabia?

teste=# --CAMPOS PRIMARY KEY NÃO ACEITAM NULOS
teste=# CREATE TABLE TSTNULL3 (campo1 INTEGER PRIMARY KEY);
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "tstnull3_pkey" na tabela "tstnull3"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL3 VALUES (1);
INSERT 0 1
teste=# INSERT INTO TSTNULL3 VALUES (NULL); --ERRO
ERRO:  valor nulo na coluna "campo1" viola a restrição não-nula
teste=#
 

* Campos PRIMARY KEY com mais de um campo também não aceitam nulos.

teste=# CREATE TABLE TSTNULL4 (campo1 INTEGER, campo2 INTEGER,
teste(# CONSTRAINT PK_TESTNULL4 PRIMARY KEY (campo1, campo2));
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "pk_testnull4" na tabela "tstnull4"
CREATE TABLE
teste=#
teste=# INSERT INTO TSTNULL4 VALUES (1, 1);
INSERT 0 1
teste=# INSERT INTO TSTNULL4 VALUES (1, NULL); --ERRO
ERRO:  valor nulo na coluna "campo2" viola a restrição não-nula
teste=#


* Consultas podem ordenar os valores nulos, colocando-os na frente ou atrás do resultado da consulta.

Para obter este efeito, empregue as cláusulas NULLS FIRST e NULLS LAST. O padrão é colocar os valores nulos por último no retorno da consulta.

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO2 NULLS FIRST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO2 NULLS LAST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)

teste=# SELECT * FROM TSTNULL ORDER BY CAMPO1 NULLS FIRST, CAMPO2 NULLS LAST;
 campo1 | campo2 | campo3
--------+--------+--------
      1 |        |      
(1 registro)


* A indexação leva em conta campos nulos. Você sabia? 

Para influenciar  este mecanismo, utilize as cláusulas NULLS FIRST e NULLS LAST.

teste=# CREATE INDEX idx_campo1_first ON TSTNULL (campo1 NULLS FIRST);
CREATE INDEX

teste=# CREATE INDEX idx_campo1_last  ON TSTNULL (campo1 NULLS LAST);
CREATE INDEX

teste=# CREATE INDEX idx_campo1_2 ON TSTNULL (campo1 NULLS LAST, campo2 NULLS FIRST);
CREATE INDEX

* Troque os nulos por um valor padrão usando o comando COPY.

Tanto no COPY FROM quanto no COPY TO, você pode substituir os nulos por um valor mais palatável, utilizando a cláusula NULL. Não deixe de indicar o valor que substituirá os nulos.


teste=# COPY TSTNULL TO 'tstnulos.txt' NULL 'NULO' CSV;
teste=# COPY TSTNULL FROM 'tstnulos.txt' NULL 'NULO' CSV;


* É possível "Forçar" valores importados para valores not null, no comando COPY FROM de arquivos CSV. 

Basta utilizar a cláusula cláusula FORCE_NOT_NULL, a partir da versão 9.0 do postgresql.Esta cláusula transforma valores nulos em strings de tamanho zero.

teste=# COPY TSTNULL FROM 'tstnulos.txt' (format csv, FORCE_NOT_NULL(campo1)); -- VALORES NULOS DE CAMPO1 SÃO LIDOS COMO STRINGS DE TAMANHO ZERO, NÃO COMO NULOS

* Você pode tratar parâmetros nulos nas funções com as cláusulas "CALLED ON NULL INPUT", "RETURNS NULL ON NULL INPUT" ou "STRICT".

Quando desejar aceitar tratar parâmetros com valor nulo em uma função, utilize a cláusula "CALLED ON NULL INPUT". O postgresql não vai retornar erro ou fazer qualquer tratamento sobre o parâmetro, passando esta responsabilidade ao programador da função.

O uso de "RETURNS NULL ON NULL INPUT" ou "STRICT" faz com que o retorno de uma função com algum parâmetro nulo, seja sempre null.

O teste é bem simples. Abaixo, coloco alguns exemplos:

CREATE OR REPLACE FUNCTION soma_nulo_1(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql CALLED ON NULL INPUT;
SELECT soma_nulo_1(1,2);
SELECT soma_nulo_1(null,2);

CREATE OR REPLACE FUNCTION soma_nulo_2(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql RETURNS NULL ON NULL INPUT;
SELECT soma_nulo_2(1,2);
SELECT soma_nulo_2(null,2);

CREATE OR REPLACE FUNCTION soma_nulo_3(i integer, j integer) RETURNS integer AS $$
        BEGIN
        IF i IS NULL OR j IS NULL THEN
            RETURN 0;
        ELSE
                RETURN (i + j);
        END IF;
        END;
$$ LANGUAGE plpgsql STRICT;
SELECT soma_nulo_3(1,2);
SELECT soma_nulo_3(null,2);

* Considerações Finais

Tratar valores nulos é de vital importância para evitar inconsistências. O postgresql oferece várias opções para lidar com este tipo de ocorrência.

4 comentários:

Unknown disse...

Cara estou com uma situação de valores nulos para criação de uma view.
Eu criei uma view para calcular o numero de vitorias(visitante) e uma de numero de vitorias(local). Porem quando eu faço a soma das duas view para criar uma 3ª view com o numero de vitorias, o postgres não soma aquelas equipes que não tiveram vitorias(visitante), por exemplo. Percebi que minhas outras views também tiveram o mesmo problema quando uma das views vier nula.

Como posso tratar essa situação?

Views:
----vitorias quando era local-------
CREATE VIEW vitoriaslocal(idequipe, vitorias) AS
SELECT idequipelocal, count(partida.idequipelocal) AS vitorias
FROM partida
WHERE partida.golsequipelocal > partida.golsequipevisitante
GROUP BY partida.idequipelocal;


----vitorias quando era visitante-------
CREATE VIEW vitoriasvisitante(idequipe, vitorias) AS
SELECT idequipevisitante, count(partida.idequipevisitante) AS vitorias
FROM partida
WHERE partida.golsequipelocal < partida.golsequipevisitante
GROUP BY idequipevisitante;

----------vitorias----------------------
CREATE VIEW totalvitorias(idequipe, vitorias) AS
select idequipe, vv.vitorias + vl.vitorias as vitorias
from vitoriasvisitante vv right join vitoriaslocal vl using (idequipe)
group by idequipe, vv.vitorias ,vl.vitorias

Unknown disse...

Estou com problema de consultas que retornam campos nulos.

Quando vou somar duas consultas(views) e numa delas um campo veio nulo, o postgres não soma.

Exemplo: criei uma view de vitorias(local) e ou vitorias(visitante), se um time não teve nenhuma vitória como visitante, ele não irá aparecer no totalvitorias(vitorias(local)+vitorias(visitante)).

como faço para resolver isso?

Cláudio Leopoldino disse...

Oi, Bruno!

Espero ter entendido sua dúvida. Você deseja fazer operações de soma com valores nulos. Bem, somando um valor nulo com um valor não nulo, resultaré em nulo sempre. Uma solução é inicializar os valores com zero antes de somar.

Abaixo coloco alguns exemplos básicos.

CREATE TEMP TABLE valor (
valor1 INTEGER NULL,
valor2 INTEGER NULL
);
INSERT INTO valor VALUES (1, null);
INSERT INTO valor VALUES (null, 1);
INSERT INTO valor VALUES (1, 1);

-- SOMA DE VALORES DISTINTOS EM QUE UM DELES PODE SER NULO

-- ESTA SINTAXE NAO RESOLVE
SELECT valor1 + valor2 AS SOMA FROM valor;

-- ESTA SINTAXE RESOLVE :)
SELECT
CASE WHEN valor1 IS NOT NULL AND valor2 IS NOT NULL THEN valor1 + valor2
ELSE 0
END AS SOMA
FROM valor;

-- SOMA DE VALORES DE UMA COLUNA COM NULOS
-- UM SUBSELECT PODE TRANSFORMAR OS NULOS EM ZEROS OU OUTRO VALOR
SELECT COUNT(*) AS CONTAGEM
FROM(
SELECT
CASE WHEN valor1 IS NULL THEN 0
ELSE valor1
END AS valor1,
CASE WHEN valor2 IS NULL THEN 0
ELSE valor2
END AS valor2
FROM valor
) AS VALOR_SEM_NULOS;

SELECT SUM(VALOR1) AS SOMA1
FROM(
SELECT
CASE WHEN valor1 IS NULL THEN 0
ELSE valor1
END AS valor1,
CASE WHEN valor2 IS NULL THEN 0
ELSE valor2
END AS valor2
FROM valor
) AS VALOR_SEM_NULOS;

SELECT SUM(VALOR2) AS SOMA2
FROM(
SELECT
CASE WHEN valor1 IS NULL THEN 0
ELSE valor1
END AS valor1,
CASE WHEN valor2 IS NULL THEN 0
ELSE valor2
END AS valor2
FROM valor
) AS VALOR_SEM_NULOS;

SELECT VALOR1 + VALOR2 AS SOMA3
FROM(
SELECT
CASE WHEN valor1 IS NULL THEN 0
ELSE valor1
END AS valor1,
CASE WHEN valor2 IS NULL THEN 0
ELSE valor2
END AS valor2
FROM valor
) AS VALOR_SEM_NULOS;

Anônimo disse...

Estou com uma consulta com PDO (php e Postgres):
$consulta = $pdo->query("SELECT userid, usernome FROM tabela WHERE usernome LIKE '%".$pesquisa."%'");
while ($linhas = $consulta->fetch(PDO::FETCH_ASSOC)) { etc...

E está funcionando direitinho.
O problema é que quando clico no botão Pesquisar sem digitar nada no campo texto (input) o Postgres traz todos os registros da tabela,
sendo que não deveria trazer nada.