PostgreSQL select all from one table and join count from table relation

Question

I have two tables, post_categories and posts. I'm trying to select * from post_categories;, but also return a temporary column with the count for each time a post category is used on a post.

Posts

| id | name | post_category_id |
| 1  | test | 1                |
| 2  | nest | 1                |
| 3  | vest | 2                |
| 4  | zest | 3                |

Post Categories

| id | name  |
| 1  | cat_1 |
| 2  | cat_2 |
| 3  | cat_3 |

Basically, I'm trying to do this without subqueries and with joins instead. Something like this, but in real psql.

select * from post_categories some-type-of-join posts, count(*)

Resulting in this, ideally.

| id | name  | count |
| 1  | cat_1 | 2     |
| 2  | cat_2 | 1     |
| 3  | cat_3 | 1     |

Your help is greatly appreciated :D

Answer 1

You can use a derived table that contains the counts per post_category_id and left join it to the post_categories table

select p.*, coalesce(t1.p_count,0)
from post_categories p
left join (
    select post_category_id, count(*) p_count
    from posts
    group by post_category_id
) t1 on t1.post_category_id = p.id