Skip to content

PostgreSQL query to select postcodes where where there are no properties

enter image description hereI have a two tables:


  • uid
  • source
  • postcode


  • 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



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.

User contributions licensed under: CC BY-SA
5 People found this is helpful