Nada a temer com Lateral Joins no PostgreSQL 9.3!

A Junção Lateral, ou lateral join, permite subselects mais legíveis e de fácil entendimento, dentro da cláusula FROM dos comandos SELECT. Através deste recurso, um item da cláusula FROM pode referenciar outro que esteja à sua esquerda, isto é, colunas de tabelas já listadas na cláusula FROM podem ser diretamente referenciadas. Lateral joins são compatíveis com CROSS, INNER ou LEFT joins.




Adicionalmente, é possível fazer lateral joins utilizando o resultado de funções executadas na cláusula FROM como tabelas.

Antes de mostrar o funcionamento desta nova funcionalidade da versão 9.3 do postgres, vamos criar algumas tabelas básicas para utilização nos exemplos.

CREATE TABLE r1 (c1 integer, c2 integer);
INSERT INTO r1 VALUES (1,1);
INSERT INTO r1 VALUES (2,2);
INSERT INTO r1 VALUES (null,null);

CREATE TABLE r2 (c1 integer, c2 integer);
INSERT INTO r2 VALUES (1,1);
INSERT INTO r2 VALUES (2,2);
INSERT INTO r2 VALUES (null,null);

* Sintaxe com CROSS JOIN

Com o uso de LATERAL, ao invés de colocar referência ao campo da tabela r1 na cláusula WHERE, a mesma é feita dentro da cláusula FROM consulta, facilitando a legibilidade e apresentando o mesmo resultado final.

Isso ocorre tanto no produto cartesiano, ou cross join, quanto nas outras modalidades de lateral join.

postgres=# SELECT * FROM r1 CROSS JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  1 |  1 |  2 |  2
  2 |  2 |  1 |  1
  2 |  2 |  2 |  2
(4 registros)


* Sintaxe com INNER JOIN

postgres=# SELECT * FROM r1 INNER JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)

* Exemplo de sintaxe com LEFT JOIN

postgres=# SELECT * FROM r1 LEFT JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
    |    |    |  
(3 registros)

* Sintaxe com JOIN tradicional.




postgres=# SELECT * FROM r1 JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)





postgres=# SELECT * FROM r1, LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL
postgres-# WHERE r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)


* Não funciona com FULL JOIN

postgres=# SELECT * FROM r1 FULL JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
ERRO:  referência inválida para tabela "r1" na cláusula FROM
LINHA 1: ...N LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS N...
                                                              ^
DETALHE:  The combining JOIN type must be INNER or LEFT for a LATERAL reference.

* Também não funciona com RIGHT JOIN

postgres=# SELECT * FROM r1 RIGHT JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
ERRO:  referência inválida para tabela "r1" na cláusula FROM
LINHA 1: ...N LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS N...
                                                              ^
DETALHE:  The combining JOIN type must be INNER or LEFT for a LATERAL reference.

* Lateral Join envolvendo três tabelas

Para fazer este teste, é necessário criar a tabela r3. Observe que a segunda junção lateral referencia os dados com base no alias RLATERAL, utilizado na primeira junção.

CREATE TABLE r3 (c1 integer, c2 integer);
INSERT INTO r3 VALUES (1,1);
INSERT INTO r3 VALUES (2,2);
INSERT INTO r3 VALUES (null,null);

postgres=# SELECT *
postgres-# FROM r1
postgres-# JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1
postgres-# JOIN LATERAL (SELECT * FROM r3 WHERE r3.c1 <> 10 and RLATERAL.c1 IS NOT NULL) AS RLATERAL2 ON RLATERAL.c1 = RLATERAL2.c1;
 c1 | c2 | c1 | c2 | c1 | c2
----+----+----+----+----+----
  1 |  1 |  1 |  1 |  1 |  1
  2 |  2 |  2 |  2 |  2 |  2
(2 registros)

* Lateral Joins com Funções

Podem ser utilizadas subconsultas com o retorno de funções e lateral join. O exemplo abaixo ilustra esta funcionalidade.

Exemplo 1: produto cartesiano com a cláusula lateral.

postgres=# SELECT * FROM r1, LATERAL (SELECT generate_series(1,3) AS SERIE) AS RLATERAL;
 c1 | c2 | serie
----+----+-------
  1 |  1 |     1
  1 |  1 |     2
  1 |  1 |     3
  2 |  2 |     1
  2 |  2 |     2
  2 |  2 |     3
    |    |     1
    |    |     2
    |    |     3
(9 registros)

Exemplo 2: referenciando tabela da função.

postgres=# SELECT * FROM r1, LATERAL (SELECT generate_series(1,3) AS SERIE WHERE r1.c1 IS NULL) AS RLATERAL;
 c1 | c2 | serie
----+----+-------
    |    |     1
    |    |     2
    |    |     3
(3 registros)
 
* Lateral Joins e Desempenho

Não foi detectada qualquer diferença em termos de desempenho entre o lateral join e o uso de subqueries. O seu uso deve ser feito em virtude da maior clareza que dá à consulta. Os exemplos abaixo corroboram esta afirmativa:

Exemplo 1: Cross Join

postgres=# EXPLAIN SELECT * FROM r1 CROSS JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Nested Loop  (cost=0.00..56731.49 rows=4532641 width=16)
   ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
         Filter: (c1 IS NOT NULL)
   ->  Materialize  (cost=0.00..47.40 rows=2129 width=8)
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(6 registros)

postgres=# EXPLAIN SELECT * FROM r1 CROSS JOIN (SELECT * FROM r2 WHERE r2.c1 <> 10) AS RLATERAL
postgres-# WHERE r1.c1 IS NOT NULL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Nested Loop  (cost=0.00..56731.49 rows=4532641 width=16)
   ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
         Filter: (c1 IS NOT NULL)
   ->  Materialize  (cost=0.00..47.40 rows=2129 width=8)
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(6 registros)


Exemplo 2: Join Tradicional

postgres=# EXPLAIN
postgres-# SELECT * FROM r1 JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
                            QUERY PLAN                           
------------------------------------------------------------------
 Merge Join  (cost=303.53..654.12 rows=22663 width=16)
   Merge Cond: (r1.c1 = r2.c1)
   ->  Sort  (cost=149.09..154.41 rows=2129 width=8)
         Sort Key: r1.c1
         ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
               Filter: (c1 IS NOT NULL)
   ->  Sort  (cost=154.44..159.76 rows=2129 width=8)
         Sort Key: r2.c1
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(10 registros)

postgres=# EXPLAIN
postgres-# SELECT * FROM r1 JOIN (SELECT * FROM r2 WHERE r2.c1 <> 10) AS RLATERAL ON r1.c1 = RLATERAL.c1
postgres-# WHERE r1.c1 IS NOT NULL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Merge Join  (cost=303.53..654.12 rows=22663 width=16)
   Merge Cond: (r1.c1 = r2.c1)
   ->  Sort  (cost=149.09..154.41 rows=2129 width=8)
         Sort Key: r1.c1
         ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
               Filter: (c1 IS NOT NULL)
   ->  Sort  (cost=154.44..159.76 rows=2129 width=8)
         Sort Key: r2.c1
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(10 registros)

* Considerações finais

Lateral joins são úteis para gerar códigos mais legíveis, evitando a separação entre as condições de junção da cláusula WHERE e a tabela referenciada na cláusula FROM dos SELECTS. Este recurso parece ser particularmente útil para comandos muito grandes, que se tornam difíceis de manter. No entanto, se os desenvolvedores forem familiarizados com a sintaxe padrão, a mesma pode ser mantida sem problemas, evitando problemas no entendimento do SQL na manutenção das consultas.

Para quem deseja maior desempenho, o comando não trouxe benefícios.

Evite o uso de lateral joins caso necessite de portabilidade com outros SGBDs, optando pelas sintaxes mais tradicionais para a realização de consultas.

PostgreSQL 9.3 Lançado!

A aguardada versão 9.3 do PostgreSQL foi oficialmente lançada hoje. Divulgue, baixe, teste e use!

As principais alterações aumentam a performance, flexibilidade e a confiabilidade do banco.

Cito como destaques:
 - Foreign data wrappers que permitem a gravação em dados externos;
- Visões atualizáveis automaticamente;
- Possibilidade de rodar pg_dump com paralelismo, aumentando a performance desta operação;
- Vários outros recursos que você pode conferir aqui.

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.