Making PostGIS Upserts Idempotent for Feature Loads

A feature load is idempotent when running it twice leaves the table exactly as running it once did. In PostGIS that means three things together: a unique constraint on a stable natural key, an INSERT … ON CONFLICT … DO UPDATE that targets that constraint, and a WHERE clause on the update that suppresses no-op writes. The third part is the one usually skipped, and it is what stops a nightly re-load from rewriting four million unchanged rows, bloating the table and firing every trigger and replication event as though the data had changed.

When to Use This Pattern#

• The source publishes a stable feature identifier — a cadastral number, a gml_id, an INSPIRE local id — that survives between deliveries.
• Deliveries overlap: today’s extract contains most of yesterday’s features, so the load is a merge rather than an append.
• You want row-level rather than delivery-level safety. Where the whole delivery is the unit, an idempotency key for the shapefile bundle is cheaper and simpler.
• Downstream consumers watch updated_at or listen to logical replication, so spurious updates are not merely wasteful but actively misleading.

The pattern does not apply when the source has no stable identifier — an extract keyed on a row number that renumbers every publication. There the honest answer is a full replace into a staging table followed by an atomic swap, not an upsert pretending to be one.

Complete Working Example#

The load below stages a batch, then merges it in one statement. The conflict target is a unique index on the natural key; the update guard compares the geometry with ST_OrderingEquals and the attributes with IS DISTINCT FROM, so identical rows are left alone.

from __future__ import annotations

from typing import Iterable, Sequence

import psycopg
from psycopg import sql

MERGE_SQL = """
INSERT INTO parcels AS p (parcel_id, geom, owner_name, area_m2, source_batch, updated_at)
SELECT s.parcel_id,
       ST_MakeValid(ST_Transform(s.geom, 25832)) AS geom,
       s.owner_name,
       s.area_m2,
       %(batch)s,
       now()
FROM   parcels_stage s
ON CONFLICT (parcel_id) DO UPDATE
   SET geom         = EXCLUDED.geom,
       owner_name   = EXCLUDED.owner_name,
       area_m2      = EXCLUDED.area_m2,
       source_batch = EXCLUDED.source_batch,
       updated_at   = now()
 WHERE p.owner_name IS DISTINCT FROM EXCLUDED.owner_name
    OR p.area_m2    IS DISTINCT FROM EXCLUDED.area_m2
    -- Geometry needs an explicit comparison: the = operator on geometry compares
    -- bounding boxes, so two different shapes with the same envelope look equal.
    OR NOT ST_OrderingEquals(p.geom, EXCLUDED.geom)
"""


def merge_batch(conn: psycopg.Connection, rows: Iterable[Sequence], batch: str) -> int:
    """Stage a delivery and merge it. Returns the number of rows actually written."""
    with conn.transaction():
        with conn.cursor() as cur:
            # An UNLOGGED staging table skips WAL for data we are about to discard.
            cur.execute(
                "CREATE UNLOGGED TABLE IF NOT EXISTS parcels_stage "
                "(parcel_id text, geom geometry(MultiPolygon, 4326), "
                " owner_name text, area_m2 double precision)"
            )
            cur.execute("TRUNCATE parcels_stage")

            with cur.copy(
                "COPY parcels_stage (parcel_id, geom, owner_name, area_m2) FROM STDIN"
            ) as copy:
                for row in rows:
                    copy.write_row(row)

            # Deduplicate WITHIN the delivery first: ON CONFLICT cannot handle a
            # conflict target hit twice by the same statement.
            cur.execute(
                "DELETE FROM parcels_stage a USING parcels_stage b "
                "WHERE a.ctid < b.ctid AND a.parcel_id = b.parcel_id"
            )

            cur.execute(MERGE_SQL, {"batch": batch})
            return cur.rowcount

The schema this depends on is unremarkable except for two constraints that carry all the weight:

CREATE TABLE parcels (
    parcel_id    text PRIMARY KEY,                       -- the natural key, not a serial
    geom         geometry(MultiPolygon, 25832) NOT NULL,
    owner_name   text,
    area_m2      double precision,
    source_batch text        NOT NULL,
    updated_at   timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT parcels_geom_valid CHECK (ST_IsValid(geom))
);

CREATE INDEX parcels_geom_gist ON parcels USING gist (geom);

parcel_id is the primary key rather than a surrogate bigserial, because a surrogate key generated at load time is different on every run and gives ON CONFLICT nothing to match. The CHECK (ST_IsValid(geom)) is the second load-bearing line: it converts “we merged a self-intersecting polygon and discovered it three joins later” into a failure at the moment of writing, where the batch id is still in scope.

Parameter & Option Reference#

Verification & Testing#

The property to assert is that the second identical run writes nothing. cur.rowcount from the merge is the direct measurement.

def test_second_identical_load_is_a_no_op(conn, delivery) -> None:
    first = merge_batch(conn, delivery.rows(), batch="2026-08-07a")
    assert first == len(delivery.rows())

    second = merge_batch(conn, delivery.rows(), batch="2026-08-07b")
    assert second == 0, "an unchanged re-load must not write any rows"

    # And a genuine change must still get through:
    changed = delivery.with_owner("12345", "New Owner AS")
    third = merge_batch(conn, changed.rows(), batch="2026-08-07c")
    assert third == 1

In production the equivalent check is a query, run right after the load, that compares the batch just written against the row count that changed:

-- A re-delivery that touched thousands of rows means either the source really
-- changed, or the guard is not catching a column that is being rewritten
-- gratuitously (a recomputed area, a re-serialised geometry).
SELECT source_batch, count(*) AS rows_touched, min(updated_at), max(updated_at)
FROM   parcels
WHERE  updated_at > now() - interval '1 hour'
GROUP  BY source_batch
ORDER  BY 1;

Common Pitfalls#

• Using = to compare geometries. In PostGIS the = operator on the geometry type compares bounding boxes, not shapes. Two entirely different polygons sharing an envelope compare equal, so the guard silently suppresses real updates. ST_OrderingEquals is the fast, exact comparison; reserve ST_Equals for the rare case where vertex order genuinely does not matter.
• A conflict target that is not unique. ON CONFLICT (parcel_id) requires a unique index on parcel_id. Without one PostgreSQL raises there is no unique or exclusion constraint matching the ON CONFLICT specification — an error that only appears once the table is big enough that someone dropped the index to speed up a load.
• Duplicates inside one delivery. ON CONFLICT cannot resolve the same key twice in a single statement; it raises ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplicate in staging first, as above, and decide deliberately which duplicate wins.
• Forgetting the SRID transform. Merging WGS84 geometry into a 25832 column raises an error if the column is typed, and silently stores mixed SRIDs if it is not. Transform at the boundary and keep CRS validation ahead of the load.
• Letting updated_at change on every run. If updated_at sits outside the guard — set unconditionally by a trigger, for instance — then every row is “changed” and the WHERE clause is decorative. Any trigger that touches the row must respect the same condition.

Frequently Asked Questions#

Related#

• Idempotency keys in spatial ETL — the delivery-level pattern this complements
• Generating idempotent keys for shapefile uploads — hashing the bundle that feeds this load
• Repairing invalid geometries before load — what to do before the CHECK rejects a row
• Streaming large GeoPackage loads into PostGIS — getting the delivery into staging efficiently

← Idempotency Keys in Spatial ETL