PostgreSQL query to select postcodes where where there are no properties

I have a two tables:

properties

• uid
• source
• postcode

postcodes

• postcode

What I’m trying to do is make a list of postcodes that have no properties grouping them by source and postcode, I had a partial success with this query:

SELECT count(prop.uid), pc.postcode, prop."source"
from properties as prop 
RIGHT OUTER JOIN postcodes as pc
ON prop.postcode = pc.postcode
GROUP BY pc.postcode, prop."source";

The problem is that is returning me null sources and what I need is to know which sources have no properties with that postcode.

Need a result like this:

count postcode source

0 “AB10 1AB” zoopla

10 “AB10 1AB” openrent

0 “AB10 1AB” spareroom

Answer

If you just want postcodes with no properties, use not exists:

select pc.*
from postcodes pc
where not exists (select 1 from properties p where p.postcode = pc.postcode);

If the postcodes have no properties, there is no corresponding source.

If you want a list of sources/postcode pairs, then use a cross join to get all combinations and the same basic logic:

select pc.*, s.source
from postcodes pc cross join
     (select distinct source from properties) s
where not exists (select 1
                  from properties p
                   where p.postcode = pc.postcode and
                         p.source = s.source
                  );

If you have a separate table of sources, use that instead of the subquery.