From 2cdf2db184fc97e1e6848e399581fc73b86ed25e Mon Sep 17 00:00:00 2001 From: Sarah Hoffmann Date: Wed, 7 Jan 2026 15:46:05 +0100 Subject: [PATCH] add NOT NULL and UNIQUE constraints where possible --- lib-sql/partition-tables.src.sql | 46 ++++++++++------- lib-sql/tables.sql | 87 ++++++++++++++++---------------- 2 files changed, 73 insertions(+), 60 deletions(-) diff --git a/lib-sql/partition-tables.src.sql b/lib-sql/partition-tables.src.sql index 937060a0..0c584185 100644 --- a/lib-sql/partition-tables.src.sql +++ b/lib-sql/partition-tables.src.sql @@ -2,36 +2,48 @@ -- -- This file is part of Nominatim. (https://nominatim.org) -- --- Copyright (C) 2022 by the Nominatim developer community. +-- Copyright (C) 2026 by the Nominatim developer community. -- For a full list of authors see the git log. drop table IF EXISTS search_name_blank CASCADE; CREATE TABLE search_name_blank ( - place_id BIGINT, - address_rank smallint, - name_vector integer[], - centroid GEOMETRY(Geometry, 4326) + place_id BIGINT NOT NULL, + address_rank smallint NOT NULL, + name_vector integer[] NOT NULL, + centroid GEOMETRY(Geometry, 4326) NOT NULL ); - {% for partition in db.partitions %} CREATE TABLE location_area_large_{{ partition }} () INHERITS (location_area_large) {{db.tablespace.address_data}}; - CREATE INDEX idx_location_area_large_{{ partition }}_place_id ON location_area_large_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}}; - CREATE INDEX idx_location_area_large_{{ partition }}_geometry ON location_area_large_{{ partition }} USING GIST (geometry) {{db.tablespace.address_index}}; + CREATE INDEX idx_location_area_large_{{ partition }}_place_id + ON location_area_large_{{ partition }} + USING BTREE (place_id) {{db.tablespace.address_index}}; + CREATE INDEX idx_location_area_large_{{ partition }}_geometry + ON location_area_large_{{ partition }} + USING GIST (geometry) {{db.tablespace.address_index}}; CREATE TABLE search_name_{{ partition }} () INHERITS (search_name_blank) {{db.tablespace.address_data}}; - CREATE INDEX idx_search_name_{{ partition }}_place_id ON search_name_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}}; - CREATE INDEX idx_search_name_{{ partition }}_centroid_street ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} where address_rank between 26 and 27; - CREATE INDEX idx_search_name_{{ partition }}_centroid_place ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} where address_rank between 2 and 25; + CREATE UNIQUE INDEX idx_search_name_{{ partition }}_place_id + ON search_name_{{ partition }} + USING BTREE (place_id) {{db.tablespace.address_index}}; + CREATE INDEX idx_search_name_{{ partition }}_centroid_street + ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} + WHERE address_rank between 26 and 27; + CREATE INDEX idx_search_name_{{ partition }}_centroid_place + ON search_name_{{ partition }} USING GIST (centroid) {{db.tablespace.address_index}} + WHERE address_rank between 2 and 25; DROP TABLE IF EXISTS location_road_{{ partition }}; CREATE TABLE location_road_{{ partition }} ( - place_id BIGINT, - partition SMALLINT, + place_id BIGINT NOT NULL, + partition SMALLINT NOT NULL, country_code VARCHAR(2), - geometry GEOMETRY(Geometry, 4326) + geometry GEOMETRY(Geometry, 4326) NOT NULL ) {{db.tablespace.address_data}}; - CREATE INDEX idx_location_road_{{ partition }}_geometry ON location_road_{{ partition }} USING GIST (geometry) {{db.tablespace.address_index}}; - CREATE INDEX idx_location_road_{{ partition }}_place_id ON location_road_{{ partition }} USING BTREE (place_id) {{db.tablespace.address_index}}; - + CREATE INDEX idx_location_road_{{ partition }}_geometry + ON location_road_{{ partition }} + USING GIST (geometry) {{db.tablespace.address_index}}; + CREATE UNIQUE INDEX idx_location_road_{{ partition }}_place_id + ON location_road_{{ partition }} + USING BTREE (place_id) {{db.tablespace.address_index}}; {% endfor %} diff --git a/lib-sql/tables.sql b/lib-sql/tables.sql index cd85def9..c7e301d5 100644 --- a/lib-sql/tables.sql +++ b/lib-sql/tables.sql @@ -34,53 +34,53 @@ GRANT SELECT ON TABLE nominatim_properties TO "{{config.DATABASE_WEBUSER}}"; drop table IF EXISTS location_area CASCADE; CREATE TABLE location_area ( - place_id BIGINT, - keywords INTEGER[], - partition SMALLINT, + place_id BIGINT NOT NULL, + keywords INTEGER[] NOT NULL, + partition SMALLINT NOT NULL, rank_search SMALLINT NOT NULL, rank_address SMALLINT NOT NULL, country_code VARCHAR(2), - isguess BOOL, + isguess BOOL NOT NULL, postcode TEXT, - centroid GEOMETRY(Point, 4326), - geometry GEOMETRY(Geometry, 4326) + centroid GEOMETRY(Point, 4326) NOT NULL, + geometry GEOMETRY(Geometry, 4326) NOT NULL ); CREATE TABLE location_area_large () INHERITS (location_area); DROP TABLE IF EXISTS location_area_country; CREATE TABLE location_area_country ( - place_id BIGINT, - country_code varchar(2), - geometry GEOMETRY(Geometry, 4326) + place_id BIGINT NOT NULL, + country_code varchar(2) NOT NULL, + geometry GEOMETRY(Geometry, 4326) NOT NULL ) {{db.tablespace.address_data}}; CREATE INDEX idx_location_area_country_geometry ON location_area_country USING GIST (geometry) {{db.tablespace.address_index}}; CREATE TABLE location_property_tiger ( - place_id BIGINT, + place_id BIGINT NOT NULL, parent_place_id BIGINT, - startnumber INTEGER, - endnumber INTEGER, - step SMALLINT, - partition SMALLINT, - linegeo GEOMETRY, + startnumber INTEGER NOT NULL, + endnumber INTEGER NOT NULL, + step SMALLINT NOT NULL, + partition SMALLINT NOT NULL, + linegeo GEOMETRY NOT NULL, postcode TEXT); GRANT SELECT ON location_property_tiger TO "{{config.DATABASE_WEBUSER}}"; drop table if exists location_property_osmline; CREATE TABLE location_property_osmline ( place_id BIGINT NOT NULL, - osm_id BIGINT, + osm_id BIGINT NOT NULL, parent_place_id BIGINT, - geometry_sector INTEGER, + geometry_sector INTEGER NOT NULL, indexed_date TIMESTAMP, startnumber INTEGER, endnumber INTEGER, step SMALLINT, - partition SMALLINT, - indexed_status SMALLINT, - linegeo GEOMETRY, + partition SMALLINT NOT NULL, + indexed_status SMALLINT NOT NULL, + linegeo GEOMETRY NOT NULL, address HSTORE, token_info JSONB, -- custom column for tokenizer use only postcode TEXT, @@ -95,27 +95,28 @@ GRANT SELECT ON location_property_osmline TO "{{config.DATABASE_WEBUSER}}"; drop table IF EXISTS search_name; {% if not db.reverse_only %} CREATE TABLE search_name ( - place_id BIGINT, - importance FLOAT, - search_rank SMALLINT, - address_rank SMALLINT, - name_vector integer[], - nameaddress_vector integer[], + place_id BIGINT NOT NULL, + importance FLOAT NOT NULL, + search_rank SMALLINT NOT NULL, + address_rank SMALLINT NOT NULL, + name_vector integer[] NOT NULL, + nameaddress_vector integer[] NOT NULL, country_code varchar(2), - centroid GEOMETRY(Geometry, 4326) + centroid GEOMETRY(Geometry, 4326) NOT NULL ) {{db.tablespace.search_data}}; -CREATE INDEX idx_search_name_place_id ON search_name USING BTREE (place_id) {{db.tablespace.search_index}}; +CREATE UNIQUE INDEX idx_search_name_place_id + ON search_name USING BTREE (place_id) {{db.tablespace.search_index}}; GRANT SELECT ON search_name to "{{config.DATABASE_WEBUSER}}" ; {% endif %} drop table IF EXISTS place_addressline; CREATE TABLE place_addressline ( - place_id BIGINT, - address_place_id BIGINT, - distance FLOAT, - cached_rank_address SMALLINT, - fromarea boolean, - isaddress boolean + place_id BIGINT NOT NULL, + address_place_id BIGINT NOT NULL, + distance FLOAT NOT NULL, + cached_rank_address SMALLINT NOT NULL, + fromarea boolean NOT NULL, + isaddress boolean NOT NULL ) {{db.tablespace.search_data}}; CREATE INDEX idx_place_addressline_place_id on place_addressline USING BTREE (place_id) {{db.tablespace.search_index}}; @@ -128,18 +129,18 @@ CREATE TABLE placex ( linked_place_id BIGINT, importance FLOAT, indexed_date TIMESTAMP, - geometry_sector INTEGER, - rank_address SMALLINT, - rank_search SMALLINT, - partition SMALLINT, - indexed_status SMALLINT, + geometry_sector INTEGER NOT NULL, + rank_address SMALLINT NOT NULL, + rank_search SMALLINT NOT NULL, + partition SMALLINT NOT NULL, + indexed_status SMALLINT NOT NULL, LIKE place INCLUDING CONSTRAINTS, wikipedia TEXT, -- calculated wikipedia article name (language:title) token_info JSONB, -- custom column for tokenizer use only country_code varchar(2), housenumber TEXT, postcode TEXT, - centroid GEOMETRY(Geometry, 4326) + centroid GEOMETRY(Geometry, 4326) NOT NULL ) {{db.tablespace.search_data}}; CREATE UNIQUE INDEX idx_place_id ON placex USING BTREE (place_id) {{db.tablespace.search_index}}; @@ -214,10 +215,10 @@ CREATE TABLE location_postcodes ( place_id BIGINT NOT NULL, parent_place_id BIGINT, osm_id BIGINT, - rank_search SMALLINT, - indexed_status SMALLINT, + rank_search SMALLINT NOT NULL, + indexed_status SMALLINT NOT NULL, indexed_date TIMESTAMP, - country_code varchar(2), + country_code varchar(2) NOT NULL, postcode TEXT NOT NULL, centroid GEOMETRY(Geometry, 4326) NOT NULL, geometry GEOMETRY(Geometry, 4326) NOT NULL -- 2.39.5