Remove postcodes also from word table when they no longer exist

[nominatim.git] / sql / update-postcodes.sql

diff --git a/sql/update-postcodes.sql b/sql/update-postcodes.sql
index a90abe25471ce5d62c12b8e4cd485e75632e1b3a..1cbc88588aa0cf04ce486cff28ac7387ebf0604b 100644 (file)
--- a/sql/update-postcodes.sql
+++ b/sql/update-postcodes.sql
@@ -48,5 +48,11 @@ INSERT INTO location_postcode
   SELECT nextval('seq_place'), 1, country_code, pc, centroid
     FROM tmp_new_postcode_locations new;
 
   SELECT nextval('seq_place'), 1, country_code, pc, centroid
     FROM tmp_new_postcode_locations new;
 

+-- Remove unused word entries
+DELETE FROM word
+    WHERE class = 'place' AND type = 'postcode'
+          AND NOT EXISTS (SELECT 0 FROM location_postcode p
+                          WHERE p.postcode = word.word);
+

 -- Finally index the newly inserted postcodes
 UPDATE location_postcode SET indexed_status = 0 WHERE indexed_status > 0;
 -- Finally index the newly inserted postcodes
 UPDATE location_postcode SET indexed_status = 0 WHERE indexed_status > 0;