How to Update a PostGIS Table from a GeoDataFrame Without Duplicates

Problem statement

The ONS publishes the UK's ward boundaries every December. The table holds the December 2023 release, and the job loads December 2024 on top:

reset_target()                                         # wards = the 2023 release
try:
    wards_2024.to_postgis("wards", engine, if_exists="append", index=False)
except Exception as exc:
    print(type(exc).__name__, str(getattr(exc, "orig", exc)).splitlines()[0])
print(q("SELECT COUNT(*) AS rows, COUNT(DISTINCT ward_code) AS wards FROM wards").to_string(index=False))

UniqueViolation duplicate key value violates unique constraint "wards_pkey"
 rows  wards
 8441   8441

Or rather, it would have: the append is refused, because this table has a primary key on ward_code and 2024 reuses most of 2023's codes. Without that key — the default for any table to_postgis creates — the same line succeeds, and the table holds both releases:

wards_2023.to_postgis("wards_nokey", engine, if_exists="replace", index=False)
wards_2024.to_postgis("wards_nokey", engine, if_exists="append", index=False)
print(q("SELECT COUNT(*) AS rows, COUNT(DISTINCT ward_code) AS wards FROM wards_nokey").to_string(index=False))

 rows  wards
16837   8787

append appends — it has no idea that E05000026 already exists. The obvious alternative is worse:

def describe(table):
    return q(f"""SELECT (SELECT COUNT(*) FROM pg_indexes WHERE tablename = '{table}') AS indexes,
                        (SELECT COUNT(*) FROM pg_constraint WHERE conrelid = '{table}'::regclass) AS constraints""")

print("before:", describe("wards").to_dict("records")[0])
wards_2024.to_postgis("wards", engine, if_exists="replace", index=False)
print("after: ", describe("wards").to_dict("records")[0])

before: {'indexes': 4, 'constraints': 1}
after:  {'indexes': 3, 'constraints': 0}

replace drops the table. With it goes the primary key, every index but the one GeoPandas recreates, the permissions you granted, any view that depended on it — and, for as long as the load takes, the table itself, from the point of view of everyone else using the database.

What you want is an upsert: insert rows that are new, update rows that exist, and leave the rest alone. to_postgis does not do it, and the reason is that it cannot know what "already exists" means for your data. That is a decision only you can make.

Quick answer

Load into a staging table, then merge with INSERT … ON CONFLICT:

def upsert(gdf, table, engine, key, *, srid=27700):
    staging = f"{table}_staging"
    gdf = gdf.to_crs(srid)
    gdf.to_postgis(staging, engine, if_exists="replace", index=False)

    cols = list(gdf.columns)
    col_list = ", ".join(f'"{c}"' for c in cols)
    updates = ", ".join(f'"{c}" = EXCLUDED."{c}"' for c in cols if c != key)
    with engine.begin() as con:
        result = con.execute(text(f"""
            INSERT INTO "{table}" ({col_list})
            SELECT {col_list} FROM "{staging}"
            ON CONFLICT ("{key}") DO UPDATE SET {updates}
        """))
        con.execute(text(f'DROP TABLE "{staging}"'))
    return result.rowcount


reset_target()
n = upsert(wards_2024, "wards", engine, key="ward_code")
print(f"{n:,} rows inserted or updated;",
      q("SELECT COUNT(*) AS rows, COUNT(DISTINCT ward_code) AS wards FROM wards").to_dict("records")[0])

8,396 rows inserted or updated; {'rows': 8787, 'wards': 8787}

ON CONFLICT requires a unique constraint on the conflict target. Without one, PostgreSQL raises:

run("CREATE TABLE wards_copy AS SELECT * FROM wards")          # same rows, no constraint
print(run("INSERT INTO wards_copy SELECT * FROM wards LIMIT 1 ON CONFLICT (ward_code) DO NOTHING"))

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Define sameness, create the table once, stage and merge

1. Decide what makes a row the same row

This is the whole problem, and it is a data question rather than a SQL one.

The ward code is a stable supplier id: GSS codes are never reused for a different area. Check it is actually unique in what arrives:

for label, gdf in [("2023", wards_2023), ("2024", wards_2024)]:
    print(f"{label}: {len(gdf):,} rows, {gdf['ward_code'].duplicated().sum()} duplicate codes, "
          f"{gdf['ward_code'].isna().sum()} null codes")

doubled = pd.concat([wards_2024, wards_2024.head(2)])       # what a supplier glitch looks like
print(f"with two rows repeated: {doubled['ward_code'].duplicated().sum()} duplicate codes")
try:
    upsert(doubled, "wards", engine, key="ward_code")
except Exception as exc:
    print(str(exc.orig).splitlines()[0])

2023: 8,441 rows, 0 duplicate codes, 0 null codes
2024: 8,396 rows, 0 duplicate codes, 0 null codes
with two rows repeated: 2 duplicate codes
ON CONFLICT DO UPDATE command cannot affect row a second time

Duplicates make the upsert fail — Postgres refuses to update the same target row twice in one statement. Resolve them before loading rather than letting the last one silently win:

gdf = gdf.sort_values("updated_at").drop_duplicates("ward_code", keep="last")

When there is no id at all, hash the geometry and the attributes that define identity:

import hashlib


def feature_key(row, cols=("ward_name", "geometry")):
    payload = "|".join(
        row.geometry.wkb.hex() if c == "geometry" else str(row[c]) for c in cols
    )
    return hashlib.sha256(payload.encode()).hexdigest()[:32]


keys_23 = set(wards_2023.apply(feature_key, axis=1))
keys_24 = set(wards_2024.apply(feature_key, axis=1))
print(f"hash keys shared by both releases: {len(keys_23 & keys_24):,} of {len(keys_24):,}")

hash keys shared by both releases: 5,073 of 8,396

Be clear about what this means: a hash over the geometry makes any coordinate change a new feature — compare that count with the 8,050 ward codes the two releases share. That is correct if the geometry defines the thing, and wrong if the same area can be redrawn or re-generalised. Hash only the parts that constitute identity.

2. Create the target table properly, once

The target needs a constraint the upsert can conflict on, and it should be created deliberately rather than inferred from a DataFrame. This is the table the setup built:

CREATE TABLE wards (
    ward_code   text PRIMARY KEY,
    ward_name   text NOT NULL,
    updated_at  timestamptz NOT NULL DEFAULT now(),
    geometry    geometry(MultiPolygon, 27700) NOT NULL
);
CREATE INDEX wards_geom_idx ON wards USING GIST (geometry);

Three deliberate choices. ward_code text PRIMARY KEY gives ON CONFLICT something to conflict on. geometry(MultiPolygon, 27700) rejects wrong-SRID and wrong-type data at the door rather than accepting it and confusing every later query — a single Polygon, which most wards are, is accepted and promoted, while a MultiPolygon into a geometry(Polygon) column is rejected. And updated_at makes it possible to answer "what changed in this release" later.

For a composite key:

ALTER TABLE wards ADD CONSTRAINT wards_key UNIQUE (lad_code, ward_code);
-- then conflict on both columns
ON CONFLICT (lad_code, ward_code) DO UPDATE SET …

3. Stage, then merge

Loading straight into the target with append and cleaning up afterwards leaves the table in a broken state in between. Staging avoids that:

def stage(gdf, table, engine, *, srid=27700, schema=None):
    staging = f"{table}_staging"
    gdf = gdf.to_crs(srid)
    gdf.to_postgis(staging, engine, if_exists="replace",
                   index=False, schema=schema, chunksize=10_000)
    with engine.begin() as con:
        con.execute(text(f'ANALYZE "{staging}"'))
    return staging

The merge then happens in one statement, inside one transaction. Either the whole batch lands or none of it does, and readers see the old state until it commits.

4. Write the merge

def merge_sql(table, staging, cols, key_cols, *, touch="updated_at"):
    col_list = ", ".join(f'"{c}"' for c in cols)
    conflict = ", ".join(f'"{c}"' for c in key_cols)
    updatable = [c for c in cols if c not in key_cols]
    sets = ", ".join(f'"{c}" = EXCLUDED."{c}"' for c in updatable)
    changed = " OR ".join(f'"{table}"."{c}" IS DISTINCT FROM EXCLUDED."{c}"' for c in updatable)
    if touch and touch not in cols:
        sets += f', "{touch}" = now()'
    return f"""
        INSERT INTO "{table}" ({col_list})
        SELECT {col_list} FROM "{staging}"
        ON CONFLICT ({conflict}) DO UPDATE
        SET {sets}
        WHERE {changed}
    """

EXCLUDED is the row that would have been inserted. The SET clause copies its values over the existing row.

The WHERE … IS DISTINCT FROM line is worth understanding: it skips the update when nothing actually changed. Comparing the columns being loaded — not the whole row, which would include updated_at and so always differ — means a re-run that brings nothing new writes nothing: no dead tuples, no index churn, no updated_at that lies about when the data last changed.

5. Handle deletions explicitly

An upsert never deletes. The 2024 release dropped wards that 2023 had; after the upsert they are still in the table:

reset_target()
staging = stage(wards_2024, "wards", engine)
with engine.begin() as con:
    con.execute(text(merge_sql("wards", staging, list(wards_2024.columns), ["ward_code"])))
    gone = con.execute(text(f"""SELECT COUNT(*) FROM wards t
                                WHERE NOT EXISTS (SELECT 1 FROM "{staging}" s WHERE s.ward_code = t.ward_code)""")).scalar()
    print(f"after the upsert: {con.execute(text('SELECT COUNT(*) FROM wards')).scalar():,} rows, "
          f"{gone:,} of them absent from the 2024 release")
    deleted = con.execute(text(f"""DELETE FROM wards t
                                   WHERE NOT EXISTS (SELECT 1 FROM "{staging}" s WHERE s.ward_code = t.ward_code)""")).rowcount
    print(f"snapshot delete removed {deleted:,}; table now {con.execute(text('SELECT COUNT(*) FROM wards')).scalar():,} rows")

after the upsert: 8,787 rows, 391 of them absent from the 2024 release
snapshot delete removed 391; table now 8,396 rows

The distinction that matters: a snapshot load contains every row that should exist, so absence means deletion — which a boundary release is. A delta load contains only what changed, so absence means "unchanged". Running the delete against a delta load empties the table. Record which kind you have; do not infer it from the row count. A soft delete — UPDATE … SET deleted_at = now() with the same NOT EXISTS — keeps the history instead.

6. Verify

def reconcile(engine, table, staging, key):
    with engine.begin() as con:
        r = con.execute(text(f"""
            SELECT
                (SELECT COUNT(*) FROM "{staging}")                        AS incoming,
                (SELECT COUNT(*) FROM "{table}")                          AS total,
                (SELECT COUNT(*) FROM "{staging}" s
                   WHERE NOT EXISTS (SELECT 1 FROM "{table}" t
                                     WHERE t."{key}" = s."{key}"))        AS missing,
                (SELECT COUNT(DISTINCT "{key}") FROM "{table}")           AS distinct_keys
        """)).mappings().one()
    assert r["missing"] == 0, f"{r['missing']} staged rows never landed"
    assert r["total"] == r["distinct_keys"], "duplicate keys in target"
    return dict(r)


print(reconcile(engine, "wards", staging, "ward_code"))

{'incoming': 8396, 'total': 8396, 'missing': 0, 'distinct_keys': 8396}

total == distinct_keys is the assertion that catches the original bug. The moment an upsert degrades into an append, those two numbers diverge — as they did in the key-less table in the problem statement.

A complete upsert and change detection

The data used on this page

The ONS ward boundaries for the whole UK, December 2023 and December 2024 releases (generalised, clipped to the coastline), from the ONS Open Geography Portal, into a local PostGIS 3.4 (the postgis/postgis:16-3.4 Docker image; connection details from PGPORT and PGPASSWORD). reset_target() rebuilds the wards table from the step 2 definition and loads the 2023 release into it.

import os
import time

import geopandas as gpd
import pandas as pd
import requests
from sqlalchemy import create_engine, text

# the server: PostGIS 3.4 in Docker on localhost:55432
if os.system("docker start guide-postgis-55432 >/dev/null 2>&1") != 0:
    os.system("docker run -d --name guide-postgis-55432 -e POSTGRES_USER=user -e POSTGRES_PASSWORD=spatialworkflow "
              "-e POSTGRES_DB=gis -p 127.0.0.1:55432:5432 postgis/postgis:16-3.4 >/dev/null")
for _ in range(60):                                  # the image restarts once while it installs PostGIS
    if os.system("docker exec guide-postgis-55432 pg_isready -h 127.0.0.1 -U user -d gis >/dev/null 2>&1") == 0:
        break
    time.sleep(2)
os.environ.setdefault("PGPORT", "55432")
os.environ.setdefault("PGPASSWORD", "spatialworkflow")
engine = create_engine("postgresql+psycopg://user@localhost/gis")
ONS = "https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services"


def q(sql):
    return pd.read_sql(text(sql), engine)


def run(sql):
    try:
        with engine.begin() as con:
            return con.execute(text(sql)).rowcount
    except Exception as exc:
        return f"ERROR: {str(getattr(exc, 'orig', exc)).splitlines()[0]}"


def release(year):
    code, name = f"WD{year % 100}CD", f"WD{year % 100}NM"
    features, offset = [], 0
    while True:                                   # the service pages at 2,000 features
        page = requests.get(f"{ONS}/Wards_December_{year}_Boundaries_UK_BGC/FeatureServer/0/query",
                            params={"where": "1=1", "outFields": f"{code},{name}", "outSR": 27700,
                                    "f": "geojson", "resultOffset": offset, "orderByFields": code},
                            timeout=300).json()
        features += page["features"]
        if not page.get("properties", {}).get("exceededTransferLimit"):
            break
        offset += len(page["features"])
    gdf = gpd.GeoDataFrame.from_features(features, crs=27700)
    return gdf.rename(columns={code: "ward_code", name: "ward_name"})[["ward_code", "ward_name", "geometry"]]


wards_2023, wards_2024 = release(2023), release(2024)


def reset_target():
    with engine.begin() as con:
        con.execute(text("DROP TABLE IF EXISTS wards, wards_staging, wards_nokey, wards_copy, wards_changes CASCADE"))
        con.execute(text("""CREATE TABLE wards (
                                ward_code   text PRIMARY KEY,
                                ward_name   text NOT NULL,
                                updated_at  timestamptz NOT NULL DEFAULT now(),
                                geometry    geometry(MultiPolygon, 27700) NOT NULL)"""))
        con.execute(text("CREATE INDEX wards_geom_idx ON wards USING GIST (geometry)"))
    wards_2023.to_postgis("wards_staging", engine, if_exists="replace", index=False)
    with engine.begin() as con:
        con.execute(text("INSERT INTO wards (ward_code, ward_name, geometry) "
                         "SELECT ward_code, ward_name, geometry FROM wards_staging"))
        con.execute(text("DROP TABLE wards_staging"))


codes = wards_2023[["ward_code"]].merge(wards_2024[["ward_code"]], how="outer", indicator=True)
print(f"2023: {len(wards_2023):,} wards   2024: {len(wards_2024):,} wards   "
      f"{codes['_merge'].value_counts().to_dict()}")

2023: 8,441 wards   2024: 8,396 wards   {'both': 8050, 'left_only': 391, 'right_only': 346}

Example 1: a complete, idempotent upsert

from contextlib import contextmanager


@contextmanager
def staging_table(gdf, table, engine, srid=27700, chunksize=10_000):
    name = f"{table}_staging"
    gdf.to_crs(srid).to_postgis(name, engine, if_exists="replace",
                                index=False, chunksize=chunksize)
    try:
        with engine.begin() as con:
            con.execute(text(f'ANALYZE "{name}"'))
        yield name
    finally:
        with engine.begin() as con:
            con.execute(text(f'DROP TABLE IF EXISTS "{name}"'))


def upsert(gdf, table, engine, key_cols, *, srid=27700,
           snapshot=False, touch="updated_at"):
    if isinstance(key_cols, str):
        key_cols = [key_cols]
    dupes = gdf.duplicated(subset=key_cols).sum()
    if dupes:
        raise ValueError(f"{dupes:,} duplicate keys in the incoming frame")
    if gdf[key_cols].isna().any().any():
        raise ValueError("null values in the key columns")

    cols = list(gdf.columns)
    with staging_table(gdf, table, engine, srid) as staging:
        with engine.begin() as con:
            before = con.execute(text(f'SELECT COUNT(*) FROM "{table}"')).scalar()
            merged = con.execute(text(
                merge_sql(table, staging, cols, key_cols, touch=touch)))
            deleted = 0
            if snapshot:
                cond = " AND ".join(f's."{c}" = t."{c}"' for c in key_cols)
                deleted = con.execute(text(f"""
                    DELETE FROM "{table}" t
                    WHERE NOT EXISTS (
                        SELECT 1 FROM "{staging}" s WHERE {cond})
                """)).rowcount
            after = con.execute(text(f'SELECT COUNT(*) FROM "{table}"')).scalar()

    inserted = after - before + deleted
    print(f"  staged   {len(gdf):,}")
    print(f"  merged   {merged.rowcount:,}  (inserted {inserted:,}, updated {merged.rowcount - inserted:,})")
    if snapshot:
        print(f"  deleted  {deleted:,}")
    print(f"  table    {before:,} → {after:,}")
    return {"merged": merged.rowcount, "deleted": deleted, "total": after}


reset_target()
print("first run:")
upsert(wards_2024, "wards", engine, "ward_code", snapshot=True)
print("same release again:")
upsert(wards_2024, "wards", engine, "ward_code", snapshot=True)

first run:
  staged   8,396
  merged   3,335  (inserted 346, updated 2,989)
  deleted  391
  table    8,441 → 8,396
same release again:
  staged   8,396
  merged   0  (inserted 0, updated 0)
  deleted  0
  table    8,396 → 8,396

Zero merged on the second run. That is the definition of idempotent, and it is what the WHERE … IS DISTINCT FROM clause buys: re-running a load is free and changes nothing. The property is discussed in general in idempotency explained.

The first run is also worth reading. Of the 8,050 wards the two releases share, 2,989 were rewritten and 5,061 left alone. Some of those rewrites are real boundary reviews; others are the generalised outline being regenerated, so a ward that did not change on the ground can still differ in a coordinate or two, and exact geometry comparison counts that as a change. If that is noise for your purpose, compare a coarser fingerprint — the geometry snapped to a grid, or its area and centroid rounded — instead of the raw geometry.

The staging table is dropped in a finally, so a failed merge does not leave a stale copy behind for the next run to be confused by.

Example 2: change detection — writing only what differs

When you want a record of what changed rather than just a current state:

from collections import Counter


def upsert_with_changelog(gdf, table, engine, key, changelog="wards_changes", srid=27700):
    cols = list(gdf.columns)
    col_list = ", ".join(f'"{c}"' for c in cols)
    sets = ", ".join(f'"{c}" = EXCLUDED."{c}"' for c in cols if c != key)
    changed = " OR ".join(f'"{table}"."{c}" IS DISTINCT FROM EXCLUDED."{c}"' for c in cols if c != key)
    with engine.begin() as con:
        con.execute(text(f'CREATE TABLE IF NOT EXISTS "{changelog}" '
                         f'(key text, action text, changed_at timestamptz)'))
    with staging_table(gdf, table, engine, srid) as staging:
        with engine.begin() as con:
            rows = con.execute(text(f"""
                WITH merged AS (
                    INSERT INTO "{table}" ({col_list})
                    SELECT {col_list} FROM "{staging}"
                    ON CONFLICT ("{key}") DO UPDATE SET {sets}
                    WHERE {changed}
                    RETURNING "{key}", (xmax = 0) AS was_insert
                )
                INSERT INTO "{changelog}" (key, action, changed_at)
                SELECT "{key}",
                       CASE WHEN was_insert THEN 'insert' ELSE 'update' END,
                       now()
                FROM merged
                RETURNING action
            """)).scalars().all()
    counts = Counter(rows)
    print(f"  {counts.get('insert', 0):,} inserted, {counts.get('update', 0):,} updated")
    return counts


reset_target()
upsert_with_changelog(wards_2024, "wards", engine, "ward_code")
print(q("SELECT action, COUNT(*) FROM wards_changes GROUP BY 1 ORDER BY 1").to_string(index=False))

  346 inserted, 2,989 updated
action  count
insert    346
update   2989

xmax = 0 is the standard trick for distinguishing an insert from an update in RETURNING: a freshly inserted tuple has no deleting transaction id, so xmax is zero, while an updated one carries the id of the transaction that superseded the old version. It relies on an implementation detail of MVCC, which is why it is worth a comment in your own code — it is correct, and it is not obvious. The counts match Example 1's first run, which is the check that the trick worked.

The whole thing is one statement, so the changelog cannot drift out of sync with the table. Splitting it into two statements introduces a window where a crash leaves them disagreeing.

Example 3: chunked upsert for a very large frame

def upsert_chunked(gdf, table, engine, key, *, chunk=2_000, srid=27700):
    """Merge in batches so one failure does not roll back every row."""
    total = {"merged": 0, "batches": 0}
    for start in range(0, len(gdf), chunk):
        part = gdf.iloc[start:start + chunk]
        staging = f"{table}_staging_{start // chunk}"
        part.to_crs(srid).to_postgis(staging, engine, if_exists="replace",
                                     index=False, chunksize=10_000)
        try:
            with engine.begin() as con:
                n = con.execute(text(
                    merge_sql(table, staging, list(part.columns), [key]))).rowcount
            total["merged"] += n
            total["batches"] += 1
            print(f"  batch {start // chunk:>3}  {len(part):>6,} staged  {n:>6,} merged")
        finally:
            with engine.begin() as con:
                con.execute(text(f'DROP TABLE IF EXISTS "{staging}"'))
    return total


reset_target()
print(upsert_chunked(wards_2024, "wards", engine, "ward_code"))

  batch   0   2,000 staged     725 merged
  batch   1   2,000 staged     751 merged
  batch   2   2,000 staged     697 merged
  batch   3   2,000 staged     919 merged
  batch   4     396 staged     243 merged
{'merged': 3335, 'batches': 5}

The trade-off is explicit and worth stating: batching gives up whole-load atomicity. A failure at batch 3 of 5 leaves batches 0–2 committed. That is usually the right trade for a very large load — a single transaction holds locks for the whole duration, generates enormous WAL, and rolling it back after twenty minutes wastes twenty minutes. The batch totals add up to Example 1's merged count.

Because the upsert is idempotent, re-running the whole job after a failure is safe: the committed batches merge to zero changes and the run continues from where it effectively stopped. Atomicity per batch plus idempotency overall gives most of what full atomicity would, at a fraction of the cost.

Why to_postgis refuses to merge

to_postgis has three modes and none of them is "merge", which looks like an omission and is really a refusal to guess. Merging requires knowing which incoming row corresponds to which existing row, and that correspondence is a property of your data model, not of the DataFrame. Two rows with identical geometry might be the same parcel re-surveyed or two different parcels stacked. Nothing in the frame distinguishes them.

ON CONFLICT is Postgres's answer, and its requirements follow from how it works. The database detects a conflict when an insert would violate a unique constraint, so there must be a unique constraint on the conflict target — the constraint is the mechanism, not a formality. EXCLUDED then exposes the rejected row so the DO UPDATE clause can use its values. This is why the pattern is atomic in a way that "check then insert" is not: a SELECT followed by an INSERT has a window between them in which another transaction can insert the same key, and ON CONFLICT has no such window.

Staging exists for atomicity and speed. Inserting a large frame row by row with per-row conflict handling means a round trip per row; loading it into a staging table first means one bulk load followed by one set-based merge, which the planner can optimise as a whole. It also means the target table is never in an intermediate state — no reader sees half a load.

The WHERE … IS DISTINCT FROM clause matters more than its size suggests. In Postgres, an update is physically a delete plus an insert: it writes a new row version, marks the old one dead, and updates every index pointing at it. Updating 4 million rows to identical values does all of that work for no change, creating 4 million dead tuples for VACUUM to reclaim. Skipping unchanged rows is often the difference between a nightly job that takes two minutes and one that takes forty.

And an upsert never deletes. This is a genuine gap rather than an oversight, because absence is ambiguous: in a snapshot load it means deletion, in a delta load it means "no news". The load must declare which it is, and the snapshot= flag in Example 1 is that declaration made explicit. Getting it wrong in the one direction empties the table.

Finally, the property this whole pattern buys is idempotency: running the same load twice produces the same state as running it once. That is what makes a scheduled job safe to retry, safe to run twice by accident, and safe to re-run after a partial failure — the same reasoning that underpins retries and timeouts in an automated job and every recoverable pipeline design.

Conflict constraints and duplicate keys

• ON CONFLICT needs a unique or exclusion constraint on the target columns, or Postgres raises immediately.
• Duplicate keys in the incoming frame cause ON CONFLICT DO UPDATE command cannot affect row a second time. Deduplicate before staging.
• if_exists="replace" drops indexes, constraints, permissions and dependent views along with the table.
• A typed geometry column enforces SRID and type. A Polygon into geometry(MultiPolygon, …) is accepted and promoted; the reverse is rejected.
• MERGE exists in PostgreSQL 15+ and is more expressive than ON CONFLICT. Its WHEN NOT MATCHED BY SOURCE branch, which would do the snapshot delete in the same statement, arrived only in PostgreSQL 17 — on 16 it is a syntax error.
• xmax = 0 in RETURNING distinguishes inserts from updates. It relies on MVCC internals — comment it.
• Partial unique indexes work as conflict targets if you name the same predicate: ON CONFLICT (id) WHERE deleted_at IS NULL.
• updated_at set from now() uses transaction start time, so every row in one batch shares a timestamp. That is usually what you want.
• Very large single transactions hold locks and generate large WAL volumes. Chunk them, and lean on idempotency for recovery.
• Run VACUUM ANALYZE after a large upsert — many dead tuples and stale statistics both hurt the next query.

Internal links

• How to write a GeoDataFrame to PostGIS — the first load, before there is anything to merge with
• Idempotency explained: why a GIS job must be safe to re-run — the property this buys
• How to load a folder of shapefiles into PostGIS — the bulk initial load
• PostGIS write fails on SRID or geometry type — what a typed column rejects
• PostGIS spatial indexes explained — what replace throws away
• How to process only the files that changed since the last run — the same idea, on files
• How to find and remove duplicate geometries in GeoPandas — deduplicating before staging
• How to record run metadata and data lineage in a GIS pipeline — what the changelog feeds

FAQ

Why does to_postgis have no upsert mode?

Because merging requires knowing which incoming row is which existing row, and only your data model defines that. GeoPandas cannot infer it from the frame.

Why do I get "no unique or exclusion constraint matching the ON CONFLICT specification"?

The conflict target has no unique constraint. Add a primary key or a unique index on exactly those columns.

What does EXCLUDED refer to?

The row that would have been inserted. In DO UPDATE SET col = EXCLUDED.col you are copying the incoming value over the existing one.

How do I delete rows that vanished from the source?

Explicitly, and only for snapshot loads: DELETE FROM target WHERE NOT EXISTS (SELECT 1 FROM staging WHERE …). Running that against a delta load empties the table.

Why should I skip unchanged rows?

An update in Postgres writes a new row version and updates every index. Updating millions of rows to identical values creates millions of dead tuples for no benefit.

Is a staging table really necessary?

For anything beyond a few thousand rows, yes. It turns row-by-row round trips into one bulk load plus one set-based merge, and keeps the target table consistent throughout.

Should I use MERGE instead?

On PostgreSQL 15 or later it is worth considering — it is more expressive, particularly for deletes. ON CONFLICT is simpler, works everywhere, and covers the common case.