The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table name: library
--------------------------------
| id | name | owner |
--------------------------------
| 1 | ABC | A |
| 2 | DEF | D |
| 3 | GHI | G |
--------------------------------
Table name: books
--------------------------------
| id | title | library |
--------------------------------
| a | xxx | 1 |
| b | yyy | 1 |
| c | zzz | 2 |
--------------------------------
Now when I do query like below:
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
The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books)
Required Output:
----------------------
| name | book_list |
----------------------
| GHI | {} | # or {null}
-----------------------
PS: I've even tried coalesce as below:
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
Postgres version: 12
Read more here: https://stackoverflow.com/questions/65712723/postgres-not-returning-data-on-array-agg-query-as-below
Content Attribution
This content was originally published by Pax at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.