Início / dba / Perguntas / 119201
Accepted
user606521
Asked: 2015-10-27 06:10:50 +0800 CST

Como unnest () trata valores NULL e matrizes vazias?

  • 772

Estou tentando conseguir algo como LEFT JOINcom a função de matriz unnest(). Eu quero que a consulta retorne uma linha com um valor nulo se a matriz estiver vazia. Portanto, usando uma CASEconstrução, eu queria passar um array falso com um único elemento nulo se o array de origem estiver vazio, mas não funciona como esperado:

Consulta 1

select element 
from (
  select array['a']::text[] as arr --< single non-null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- returns 1 row with element = "a"

Consulta 2

select element 
from (
  select array[]::text[] as arr --< empty array
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- returns 0 rows (should return 1 row with element = NULL?)

Consulta 3

select element 
from (
  select array[null]::text[] as arr --< single null element
) sub, unnest(
  (
    case when array_length(sub.arr, 1) <= 0 then (array[null])::text[] 
    else sub.arr 
    end
  )
) element
-- return single row with element = NULL

Acabei de descobrir que select array_length(array[]::text[], 1)retorna NULL- minha pergunta é por quê?

postgresql postgresql-9.4

1 respostas

  1. Best Answer

    Para citar Tom Lane no tópico "Should array_length () Return NULL" em pgsql-hackers :

    O problema é que essa sintaxe cria uma matriz de dimensões zero, não uma que tenha 1 dimensão e zero elementos. Portanto, "0" seria incorreto.

    Difícil dizer se array_length()deve retornar NULLou 0para uma dimensão de array vazia...

    Solução

    Você pode inverter a lógica e processar a matriz de origem original somente se o comprimento for >= 1:

    WITH tbl(id, arr) AS (
   VALUES
     (1, '{a}'::text[])
   , (2, '{NULL}'::text[])
   , (3, '{}'::text[])
   , (4, '{x,y,z}'::text[])
   )
SELECT id, elem
FROM   tbl t
     , unnest (
          CASE WHEN array_length(t.arr, 1) >= 1
               THEN t.arr
               ELSE '{null}'::text[] END
       ) elem;

    Retorna pelo menos uma linha para cada linha de origem.

    Ou, muito mais simples , use um realLEFT JOIN (a vírgula em sua FROMcláusula é uma notação curta para CROSS JOIN):

    SELECT id, elem
FROM   tbl t
LEFT   JOIN LATERAL unnest(t.arr) elem ON true;

    Isso preenche valores NULL automaticamente para as colunas da tabela correta se nenhuma correspondência for encontrada. Exatamente o que você está tentando alcançar.
    Relacionado:

    db<>mexa aqui

    • 9

relate perguntas