PostgreSQL – Array_Agg Query Not Returning Data

arraycoalescejoin;postgresqlsqlalchemy

The problem arise when there are no data for books in specific library. Consider a following working scenario.

Table library

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

Table 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}
-----------------------

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

Best Answer

A LEFT JOIN can solve it, like Laurenz provided.

But I suggest an ARRAY constructor in a LATERAL subquery instead:

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;

This way, you don't need to aggregate in the outer query level and don't need to GROUP BY there.

You also don't need COALESCE, since the ARRAY constructor over an empty result already produces an empty array ({}).

And it should be faster for a small selection in library - obviously the query gets the result for a single given library.

Aside, you only need the variable :library_no in a single place like demonstrated.

About LATERAL joins:

What is the difference between LATERAL and a subquery in PostgreSQL?

About the ARRAY constructor:

Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Basic about joining tables in the manual.

Related Solutions

Mysql – Simple select statement not returning correct data

You are missing a JOIN between prices and items.

Your implicit join in the where clause only joins vendors and prices so you get a cartesian join for the result of that join and the items table. If you had used ANSI JOIN keywords instead, you would have noticed that immediately

You need something like this:

select distinct items.name, 
       vendors.name, 
       prices.url, 
       prices.price 
 FROM vendors
   JOIN prices ON vendors.vid = prices.vid 
   JOIN items ON items.iid = prices.iid ---  this part is missing
 WHERE items.iid='3' 
ORDER BY prices.price;

I don't know if the join condition is correct, because you didn't tell us which column in prices contains the foreign key to items.

Postgresql – Combine a generated series with each row from a select

You need to do a left outer join on the generate_series with an ON clause that matches the date of each report.

Without a schema or sample data it's a bit fiddly to cook up an example modification of your actual query.

Here's a simplified example to show you how it works:

CREATE TABLE sparse_dates(
    bakedgood text primary key,
    firstbaked date not null
);

INSERT INTO sparse_dates (bakedgood, firstbaked) VALUES
('tart', '2012-02-01'),
('baguette', '2012-02-01'),
('cookie', '2012-03-01'),
('macaron', '2012-08-01');

To get the number of baked goods first baked on a given month:

SELECT monthtimestamp, count(bakedgood) 
FROM generate_series( 
    (SELECT min(firstbaked) FROM sparse_dates),
    (SELECT max(firstbaked) FROM sparse_dates),
    INTERVAL '1' MONTH) 
    AS monthtimestamp
LEFT OUTER JOIN sparse_dates ON (monthtimestamp = firstbaked)
GROUP BY monthtimestamp;

with output like:

     monthtimestamp     | count 
------------------------+-------
 2012-02-01 00:00:00+08 |     2
 2012-03-01 00:00:00+08 |     1
 2012-04-01 00:00:00+08 |     0
 2012-05-01 00:00:00+08 |     0
 2012-06-01 00:00:00+08 |     0
 2012-07-01 00:00:00+08 |     0
 2012-08-01 00:00:00+08 |     1
(7 rows)

Best Answer

Related Solutions

Mysql – Simple select statement not returning correct data

Postgresql – Combine a generated series with each row from a select

Related Question