⚙️ How-To PostGIS & databases Files & formats Spatial SQL & PostGIS · step 7 of 15 Updated

How to Write a GeoDataFrame to PostGIS

Problem statement

to_postgis() is one line, and it works the first time. It is the second time that causes problems.

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

That call silently drops the table, recreates it without your indexes, without your constraints, without your primary key, and infers every column type from whatever pandas happened to have in memory. A surveyed column of dates becomes text because one row was null. The spatial index you created last month is gone, and the query that ran in 11 ms now takes four seconds.

Then there is the write that fails outright:

psycopg.errors.UndefinedObject: type "geometry" does not exist
ValueError: Cannot determine common CRS for concatenated object
psycopg.errors.NotNullViolation: null value in column "geom" violates not-null constraint
sqlalchemy.exc.ProgrammingError: column "surveyDate" of relation "parcels" does not exist

Writing spatial data to a database well means deciding three things to_postgis defaults badly on: what happens to the existing table, what types the columns get, and what happens when it fails halfway.

Quick answer

Create the table deliberately once, then only ever append or upsert into it:

import geopandas as gpd
from sqlalchemy import create_engine, text

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

# 1. the table, its types, its constraints and its index — defined once, in SQL
with engine.begin() as conn:
    conn.execute(text("""
        CREATE TABLE IF NOT EXISTS parcels (
            parcel_id bigint PRIMARY KEY,
            ward      text NOT NULL,
            surveyed  date,
            geom      geometry(MultiPolygon, 27700) NOT NULL
        );
        CREATE INDEX IF NOT EXISTS parcels_geom_idx ON parcels USING GIST (geom);
    """))

# 2. make the frame match the table before writing
gdf = gdf.to_crs(27700)
gdf.columns = [c.lower() for c in gdf.columns]
gdf["surveyed"] = pd.to_datetime(gdf["surveyed"]).dt.date
gdf = gdf.explode(index_parts=False) if False else gdf   # see step 3 below

# 3. append, never replace
gdf.to_postgis("parcels", engine, if_exists="append", index=False)

The rules that follow from this:

Do Instead of
if_exists="append" if_exists="replace"
CREATE TABLE in SQL letting pandas infer types
lowercase column names mixed case that needs quoting everywhere
.to_crs(target) before writing hoping the SRIDs match
a declared geometry(Type, SRID) untyped geometry
chunksize= for big frames one enormous transaction

What replace actually destroys

Checklist of table features lost when if_exists=replace drops and recreates a table.
Everything that made the table a database table rather than a spreadsheet.

Step-by-step solution

Vertical steps from reprojecting through normalising columns and types to appending in chunks and verifying.
Five steps. The first four are about matching the table you already defined.

1. Match the CRS before you write, not after

PostGIS stores an SRID on the geometry column and enforces it. A frame in EPSG:4326 written to a geometry(Polygon, 27700) column is rejected — which is good, but only if you notice.

TARGET_SRID = 27700

if gdf.crs is None:
    raise ValueError("layer has no CRS; refusing to guess")
if gdf.crs.to_epsg() != TARGET_SRID:
    gdf = gdf.to_crs(TARGET_SRID)

Never set the CRS with gdf.set_crs(27700, allow_override=True) to make an error go away. That relabels the coordinates without moving them, and puts your data in the North Sea. See cannot transform naive geometries for the difference.

2. Normalise column names before PostgreSQL does it for you

PostgreSQL folds unquoted identifiers to lowercase. A column called surveyDate becomes surveydate in the table but is referenced as "surveyDate" in any query that quotes it — so half your SQL fails and the other half works.

import re

def normalise_columns(gdf):
    gdf = gdf.rename(columns=lambda c: re.sub(r"[^0-9a-z_]", "_", c.strip().lower()))
    # a leading digit is a syntax error as a bare identifier
    gdf = gdf.rename(columns=lambda c: f"c_{c}" if c[0].isdigit() else c)
    return gdf

Do this once, at the boundary. Every downstream query gets simpler.

3. Fix the geometry type mismatch deliberately

A declared geometry(Polygon, 27700) column rejects a MultiPolygon. Real datasets contain both, usually because one feature is an island pair.

Two honest options:

# Option A — declare the column as MultiPolygon and promote everything
from shapely.geometry import MultiPolygon

gdf["geometry"] = [
    g if g.geom_type == "MultiPolygon" else MultiPolygon([g])
    for g in gdf.geometry
]

# Option B — declare Polygon and split multiparts into separate rows
gdf = gdf.explode(index_parts=False).reset_index(drop=True)

Verified behaviour worth knowing: PostGIS promotes automatically in this direction. Inserting a single-part Polygon into a geometry(MultiPolygon, …) column succeeds and stores a one-part MultiPolygon, so option A needs only the column declaration — the Python loop above just makes the frame match what will be stored, which keeps geom_type assertions honest on both sides.

Option A keeps one row per real-world feature and is usually right. Option B changes your row counts, which matters if anything downstream sums by row — see how to split multipart geometries.

The option that is not honest is declaring the column as bare geometry, which accepts anything and moves the problem to whoever reads the table next.

4. Set the types you care about explicitly

to_postgis accepts a dtype mapping, and it is worth using for anything a null could confuse:

from sqlalchemy.types import BigInteger, Date, Text
from geoalchemy2 import Geometry

gdf.to_postgis(
    "parcels", engine, if_exists="append", index=False,
    dtype={
        "parcel_id": BigInteger(),
        "ward": Text(),
        "surveyed": Date(),
        "geom": Geometry("MULTIPOLYGON", srid=27700),
    },
)

Without this, a date column with one NaT is inferred as text, and it stays text forever because the table already exists.

5. Chunk large writes, and know what a failure leaves behind 

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

chunksize controls how many rows go per INSERT, not how many transactions there are. A failure partway through still rolls back the whole write if it runs inside one transaction — which is what you want, and what engine.begin() gives you:

with engine.begin() as conn:                       # one transaction, commits or rolls back
    gdf.to_postgis("parcels", conn, if_exists="append", index=False, chunksize=10_000)

Passing engine instead of conn uses autocommit-per-chunk, so a failure at chunk 40 leaves 390,000 rows behind and no easy way to tell which.

6. Verify the write, in the database 

with engine.connect() as conn:
    checks = conn.execute(text("""
        SELECT count(*)                                  AS rows,
               count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid,
               count(DISTINCT ST_SRID(geom))             AS srids,
               min(ST_SRID(geom))                        AS srid
        FROM parcels
    """)).mappings().one()

assert checks["invalid"] == 0, f"{checks['invalid']} invalid geometries landed"
assert checks["srids"] == 1 and checks["srid"] == TARGET_SRID
print(f"{checks['rows']:,} rows, SRID {checks['srid']}")

Counting in Python before the write tells you what you meant to send. Counting in SQL after it tells you what arrived.

Code examples

Example 1: an idempotent upsert instead of replace

The real requirement is usually "make the table match this frame" — which is an upsert, not a replace.

from sqlalchemy import text

def upsert_parcels(gdf, engine, key="parcel_id"):
    """Load into a temp table, then merge on the key. Safe to re-run."""
    gdf.to_postgis("parcels_staging", engine, if_exists="replace", index=False)

    with engine.begin() as conn:
        result = conn.execute(text(f"""
            INSERT INTO parcels (parcel_id, ward, surveyed, geom)
            SELECT parcel_id, ward, surveyed, geom FROM parcels_staging
            ON CONFLICT ({key}) DO UPDATE SET
                ward     = EXCLUDED.ward,
                surveyed = EXCLUDED.surveyed,
                geom     = EXCLUDED.geom
            WHERE parcels.geom IS DISTINCT FROM EXCLUDED.geom
               OR parcels.ward IS DISTINCT FROM EXCLUDED.ward
        """))
        conn.execute(text("DROP TABLE parcels_staging"))
    return result.rowcount

The WHERE ... IS DISTINCT FROM clause makes it cheap to re-run: unchanged rows are not rewritten, so the table's update timestamps stay meaningful. That is the idempotency property that makes a job safe to run twice.

Example 2: writing a whole folder, one table per file 

from pathlib import Path

def load_folder(folder: Path, engine, srid=27700):
    report = []
    for path in sorted(folder.glob("*.gpkg")):
        table = re.sub(r"[^0-9a-z_]", "_", path.stem.lower())
        try:
            gdf = normalise_columns(gpd.read_file(path).to_crs(srid))
            with engine.begin() as conn:
                gdf.to_postgis(table, conn, if_exists="replace", index=False)
                conn.execute(text(
                    f'CREATE INDEX IF NOT EXISTS {table}_geom_idx ON {table} USING GIST (geom)'
                ))
                conn.execute(text(f'ANALYZE {table}'))
            report.append((path.name, table, len(gdf), "ok"))
        except Exception as exc:
            report.append((path.name, table, 0, f"{type(exc).__name__}: {exc}"))
    return report

if_exists="replace" is defensible here — this is a bulk load of files into fresh tables, and the index is recreated in the same transaction. The rule is not "never replace", it is "never replace a table someone else has configured".

Example 3: appending only what is new 

def append_new_only(gdf, engine, table="parcels", key="parcel_id"):
    existing = pd.read_sql(text(f"SELECT {key} FROM {table}"), engine)[key]
    new = gdf[~gdf[key].isin(existing)]
    if new.empty:
        return 0
    with engine.begin() as conn:
        new.to_postgis(table, conn, if_exists="append", index=False)
    return len(new)

Fine up to a few million keys. Past that, load into a staging table and let the database do the anti-join — pulling every key into pandas stops being the cheap option.

Explanation

Flow from GeoDataFrame through CRS, naming, typing and geometry checks into a transactional append.
Every gate before the write is a class of error the database would otherwise raise mid-load.

to_postgis is a thin wrapper over pandas' to_sql plus GeoAlchemy2's geometry type. Understanding what each layer does explains most of the surprises.

Pandas decides the schema when the table does not exist. It maps dtypes to SQL types, and pandas dtypes are lossy about intent: object becomes text, a float column with integers stays double precision, and anything with a null gets widened. This is why creating the table in SQL first is worth the extra six lines — you are the one who knows a parcel_id is a bigint and a surveyed is a date.

GeoAlchemy2 handles the geometry column. It converts each shape to EWKB — the extended well-known binary format that carries an SRID alongside the coordinates. That SRID comes from the GeoDataFrame's CRS, which is why a frame with crs=None produces geometries with SRID 0, and why they then fail to insert into a typed column.

PostGIS enforces the column declaration. geometry(MultiPolygon, 27700) is a constraint, checked on every row. The errors it produces feel obstructive on the first load and become the reason you trust the table six months later — nothing wrong got in, because it could not.

The last piece is transactional behaviour. to_postgis(gdf, engine) opens its own connection and commits per batch. to_postgis(gdf, conn) inside with engine.begin() participates in your transaction, so the write is all-or-nothing. For a nightly load, the second is almost always what you want: a half-loaded table is worse than no load, because the job reports success and the data is wrong.

Edge cases or notes

  • geom vs geometry as a column name. GeoPandas defaults to geometry; the PostGIS convention is geom. Rename with gdf.rename_geometry("geom") so read_postgis(geom_col="geom") works without thinking.
  • Null geometries are rejected by a NOT NULL column and accepted by a nullable one. Decide deliberately; a nullable geometry column is a permanent source of confusing joins.
  • Empty geometries insert fine and match nothing. Filter with ~gdf.geometry.is_empty before writing.
  • Very large writes benefit from COPY, not INSERT. For tens of millions of rows, write to a CSV of EWKB and COPY it, or use ogr2ogr -f PostgreSQL.
  • ANALYZE after a bulk load. Without statistics the planner may keep choosing sequential scans on a freshly loaded table.
  • A bigserial primary key conflicts with supplying your own ids. Either let the database generate them and do not send the column, or use plain bigint and own the values.
  • Reserved words make terrible column names. order, user, end and references all need quoting forever. Rename them at the boundary.
  • Writing from multiple processes into one table is fine — that is what the database is for — but each needs its own connection, not a shared engine forked across workers.

FAQ

Why does to_postgis drop my spatial index?

if_exists="replace" drops the table and creates a new one. Indexes, constraints and primary keys belong to the old table and go with it. Use append, or recreate the index in the same transaction.

What is the difference between passing engine and passing conn?

engine gets its own connection and commits per batch, so a mid-write failure leaves partial data. conn from with engine.begin() joins your transaction, so the write is all-or-nothing.

How do I stop pandas guessing my column types?

Create the table in SQL first and append into it, or pass an explicit dtype mapping. Both work; the first also gets you constraints and indexes.

My geometry column ends up as SRID 0. Why?

The GeoDataFrame had crs=None, so GeoAlchemy2 had nothing to write. Set the CRS correctly with to_crs — or set_crs only if the coordinates genuinely are in that CRS and were merely unlabelled.

Can I write a mix of Polygons and MultiPolygons?

Not into a geometry(Polygon, …) column. Promote everything to MultiPolygon, explode everything to single parts, or declare the column as geometry(Geometry, …) and accept that readers must handle both.

How fast is to_postgis for millions of rows?

Adequate with chunksize, but INSERT-based. For very large loads, ogr2ogr -f PostgreSQL or a COPY of EWKB is several times faster.

Should the table have a primary key?

Yes. Without one you cannot upsert, cannot reliably delete a single row, and cannot join the table to itself. parcel_id from the source data is better than a generated id if it is genuinely stable.