How to Update a PostGIS Table from a GeoDataFrame Without Duplicates #

Problem statement #

The nightly job loads yesterday's parcel updates:

gdf.to_postgis("parcels", engine, if_exists="append", index=False)

After a week the table has 28 million rows and 4 million parcels. Every run appended a full copy, because append appends — it has no idea that id = 41882 already exists.

The obvious alternative is worse:

gdf.to_postgis("parcels", engine, if_exists="replace", index=False)

replace drops the table. With it goes the spatial index, the primary key, every other index, the permissions you granted, any view that depended on it, and — for the several seconds 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:

import geopandas as gpd
from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg://user@localhost/gis")

def upsert(gdf, table, engine, key, *, geom_col="geometry", 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

n = upsert(gdf, "parcels", engine, key="parcel_id")
print(f"{n:,} rows inserted or updated")

ON CONFLICT requires a unique constraint on the conflict target. Without one, PostgreSQL raises there is no unique or exclusion constraint matching the ON CONFLICT specification.

Step-by-step solution #

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.

# is the candidate key actually unique in the incoming data?
dupes = gdf["parcel_id"].duplicated().sum()
nulls = gdf["parcel_id"].isna().sum()
print(f"{len(gdf):,} rows, {dupes:,} duplicate ids, {nulls:,} null ids")

178,204 rows, 12 duplicate ids, 0 null ids

Twelve duplicates will make the upsert fail with ON CONFLICT DO UPDATE command cannot affect row a second time — 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("parcel_id", 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=("class", "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]

gdf["feature_key"] = gdf.apply(feature_key, axis=1)

Be clear about what this means: a hash over the geometry makes any coordinate change a new feature. That is correct if the geometry defines the thing, and wrong if the same parcel can be re-surveyed. 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:

CREATE TABLE parcels (
    parcel_id   text PRIMARY KEY,
    class       text NOT NULL,
    area_m2     double precision,
    updated_at  timestamptz NOT NULL DEFAULT now(),
    geometry    geometry(MultiPolygon, 27700) NOT NULL
);
CREATE INDEX parcels_geom_idx ON parcels USING GIST (geometry);
CREATE INDEX parcels_class_idx ON parcels (class);

Three deliberate choices. parcel_id 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 is accepted and promoted, while a MultiPolygon into a geometry(Polygon) column is rejected. And updated_at makes it possible to answer "what changed last night" later.

For a composite key:

ALTER TABLE parcels ADD CONSTRAINT parcels_key UNIQUE (authority_code, parcel_id);

-- then conflict on both columns
ON CONFLICT (authority_code, parcel_id) 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)
    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 "{table}" IS DISTINCT FROM EXCLUDED
    """

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

The WHERE … IS DISTINCT FROM EXCLUDED line is worth understanding: it skips the update when nothing actually changed. On a nightly load where 99% of rows are identical to yesterday, this avoids writing 4 million unchanged rows — 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. If a parcel disappears from the source, it stays in the table forever unless you decide otherwise:

-- soft delete: keep the row, mark it gone
UPDATE parcels t
SET deleted_at = now()
WHERE NOT EXISTS (SELECT 1 FROM parcels_staging s WHERE s.parcel_id = t.parcel_id)
  AND deleted_at IS NULL;

-- hard delete: only when the load is known to be a complete snapshot
DELETE FROM parcels t
WHERE NOT EXISTS (SELECT 1 FROM parcels_staging s WHERE s.parcel_id = t.parcel_id);

The distinction that matters: a snapshot load contains every row that should exist, so absence means deletion. 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.

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, "parcels", "parcels_staging", "parcel_id"))

{'incoming': 178204, 'total': 4012884, 'missing': 0, 'distinct_keys': 4012884}

total == distinct_keys is the assertion that catches the original bug. The moment an upsert degrades into an append, those two numbers diverge.

Code examples #

Example 1: a complete, idempotent upsert #

from contextlib import contextmanager
import geopandas as gpd
from sqlalchemy import create_engine, text

@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 = [c for c in 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()

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

engine = create_engine("postgresql+psycopg://user@localhost/gis")
gdf = gpd.read_file("parcels_2026_08_21.gpkg")
upsert(gdf, "parcels", engine, "parcel_id", snapshot=False)

  staged   178,204
  merged   178,204  (inserted 1,204, updated 176,000)
  table    4,011,680 → 4,012,884

Run it again on the same file:

  staged   178,204
  merged   0  (inserted 0, updated 0)
  table    4,012,884 → 4,012,884

Zero merged on the second run. That is the definition of idempotent, and it is what the WHERE … IS DISTINCT FROM EXCLUDED clause buys: re-running a load is free and changes nothing. Verified against PostGIS 3.4 — a repeated upsert of identical rows reports INSERT 0 0. The property is discussed in general in idempotency explained.

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:

def upsert_with_changelog(gdf, table, engine, key, changelog="parcels_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)

    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 "{table}" IS DISTINCT FROM EXCLUDED
                    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()
    from collections import Counter
    counts = Counter(rows)
    print(f"  {counts.get('insert', 0):,} inserted, {counts.get('update', 0):,} updated")
    return counts

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 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 #

import geopandas as gpd
from sqlalchemy import create_engine, text

def upsert_chunked(gdf, table, engine, key, *, chunk=100_000, srid=27700):
    """Merge in batches so one failure does not roll back millions of rows."""
    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:
                cols = list(part.columns)
                n = con.execute(text(
                    merge_sql(table, staging, cols, [key]))).rowcount
            total["merged"] += n
            total["batches"] += 1
            print(f"  batch {start // chunk:>3}  {len(part):>8,} staged  {n:>8,} merged")
        finally:
            with engine.begin() as con:
                con.execute(text(f'DROP TABLE IF EXISTS "{staging}"'))
    return total

upsert_chunked(gpd.read_file("national_parcels.gpkg"), "parcels", engine, "parcel_id")

The trade-off is explicit and worth stating: batching gives up whole-load atomicity. A failure at batch 30 of 41 leaves batches 0–29 committed. That is usually the right trade for a 4-million-row load — a single transaction holds locks for the whole duration, generates enormous WAL, and rolling it back after twenty minutes wastes twenty minutes.

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.

Explanation #

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 EXCLUDED 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.

Edge cases or notes #

• 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, including a WHEN NOT MATCHED BY SOURCE delete branch.
• 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.