Postgres not returning data on array_agg query as below

Question

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

Please add the desired output by editing the question

Akhilesh Mishra
– Akhilesh Mishra

2021-01-14 03:25:01 +00:00
Commented Jan 14, 2021 at 3:25 — Akhilesh Mishra
– Akhilesh Mishra, Commented Jan 14, 2021 at 3:25
Here you go @AkhileshMishra

PaxPrz
– PaxPrz

2021-01-14 03:27:29 +00:00
Commented Jan 14, 2021 at 3:27 — PaxPrz
– PaxPrz, Commented Jan 14, 2021 at 3:27

Akhilesh Mishra · Accepted Answer · 2021-01-14 04:14:33Z

2

You should use left join for this kind of scenarios like below:

select 
    l.name, 
    array_agg(b.title) 
from library l left join books b on l.id=b.library
where l.id=3      -- here you can pass you library ID using :library_no variable
group by l.name

DEMO

answered Jan 14, 2021 at 4:14

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

PaxPrz Over a year ago

Yes it works. but shouldn't coalesce return an empty array when there is no data?

Akhilesh Mishra Over a year ago

it will return {NULL}. if you will use coalesce then it will return {""}. actually {NULL} and {} is same.

PaxPrz Over a year ago

But in my case, no column is returned in case of coalesce also

Akhilesh Mishra Over a year ago

what exactly you are looking for?

PaxPrz Over a year ago

The problem is solved using your solution. but couldn't the problem solved with coalesce as above 'PS'

Collectives™ on Stack Overflow

Postgres not returning data on array_agg query as below

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related