Início / dba / Perguntas / 283198
Accepted
PaxPrz
Asked: 2021-01-14 19:01:58 +0800 CST

Postgres não retornando dados na consulta array_agg como abaixo

  • 772

O problema surge quando não há dados para livros em uma biblioteca específica. Considere um cenário de trabalho a seguir.

Mesalibrary

--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

Mesabooks

--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     xxx     |     1     |
|  b |     yyy     |     1     |
|  c |     zzz     |     2     |
--------------------------------

Agora, quando eu faço consulta como abaixo:

SELECT library.name, array_agg(b.title) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

A consulta gera saída para a biblioteca 1 e 2, mas não para a biblioteca 3. Por que e como resolver esse problema? (Gerar uma lista vazia em nenhum livro da biblioteca)

Saída necessária:

----------------------
| name |    book_list |
----------------------
|  GHI |      {}      |   # or {null}
-----------------------

Eu até tentei coalescecomo abaixo:

SELECT library.name, coalesce(array_agg(b.title), ARRAY[]::VARCHAR[]) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

Versão do Postgres: 12

postgresql join

2 respostas

  1. Best Answer

    A LEFT JOINpode resolvê-lo, como Laurenz forneceu.

    Mas sugiro um construtor ARRAY em uma LATERALsubconsulta:

    SELECT l.name, b.book_list
FROM   library l
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT title
      FROM   books
      WHERE  library = l.id
      )
   ) b(book_list)
WHERE  l.id = :library_no;

    Dessa forma, você não precisa agregar no nível de consulta externa e não precisa fazer GROUP BYisso.

    Você também não precisa COALESCE, pois o construtor ARRAY sobre um resultado vazio já produz um array vazio ( {}).

    E deve ser mais rápido para uma pequena seleção library- obviamente, a consulta obtém o resultado para uma única biblioteca.

    Além disso, você só precisa da variável :library_noem um único local, como demonstrado.

    Sobre LATERALas junções:

    Sobre o construtor ARRAY:

    Básico sobre como juntar tabelas no manual .

    • 9

  2. Você precisa de uma junção à esquerda:

    ... FROM library
       LEFT JOIN books ON library.id = books.library

    Então você também terá bibliotecas sem livros.

    • 5

relate perguntas