Import a Layer into PostGIS with PyQGIS

Moving data into PostGIS is the step that turns a folder of files into a shared, queryable, multi-user dataset. QGIS can import any layer it can read — shapefiles, GeoPackages, CSVs, web services, memory layers — into a PostGIS table, creating the table with the right columns, geometry type and CRS. Doing it from Python makes imports repeatable: the same naming, keys, indexes and checks every time, for one layer or a hundred.

This recipe belongs to PostGIS & Database Workflows. It imports a layer with Processing, controls the table definition, uses the connections API for finer control, creates indexes and statistics, imports a folder of files, and verifies the result. Appending to tables that already exist is covered in appending features to a PostGIS table.

What an import decidesAn import turns a source layer into a PostGIS table. It decides the schema and table name, the primary key column, the geometry column name and type, the CRS stored in the geometry column, how field names are converted, and whether an existing table is overwritten. After loading, a spatial index and table statistics make the table fast to query.Source layer → well-defined tablesourceshp, gpkg, csv,memory, WFStable definitionschema.tableprimary keygeom column + typeCRS (SRID)overwrite?after loadspatial indexANALYZE

Prerequisites

  • QGIS 3.34 LTR or newer, or the QGIS 4 series.
  • A PostgreSQL database with the PostGIS extension and a saved QGIS connection, as set up in connecting to a PostGIS database.
  • A database role allowed to create tables in the target schema.

Import with Processing

native:importintopostgis (shown as "Export to PostgreSQL" in the toolbox) takes a layer and a stored connection name, and creates the table.

import processing
from qgis.core import QgsProject, QgsVectorLayer

src = QgsVectorLayer("/data/inbox/hydrants.gpkg|layername=hydrants", "hydrants", "ogr")
processing.run("native:importintopostgis", {
    "INPUT": src,
    "DATABASE": "assets_db",          # name of the saved QGIS connection
    "SCHEMA": "water",
    "TABLENAME": "hydrants",
    "PRIMARY_KEY": "hydrant_pk",
    "GEOMETRY_COLUMN": "geom",
    "ENCODING": "UTF-8",
    "OVERWRITE": True,
    "CREATEINDEX": True,
    "LOWERCASE_NAMES": True,
    "DROP_STRING_LENGTH": False,
    "FORCE_SINGLEPART": False,
})
print("imported", src.featureCount(), "features into water.hydrants")

Breakdown: DATABASE is the name of a connection saved in QGIS, so no password appears in the script; authentication comes from the connection's stored configuration. The primary key column is created as a serial integer if the source has no suitable key. LOWERCASE_NAMES converts field names to lower case, which saves endless double-quoting in SQL — PostgreSQL folds unquoted identifiers to lower case, so a column named HydrantID would otherwise have to be written "HydrantID" forever. CREATEINDEX builds a GiST spatial index on the geometry. OVERWRITE replaces an existing table of the same name, which is right for a reload and dangerous for anything else.

Control geometry type and CRS

PostGIS geometry columns are typed: MultiPolygon with SRID 25832, for example. A source mixing single and multipart polygons, or in an unexpected CRS, needs adjusting so the table's type is right from the start.

Getting the column type rightA source with both Polygon and MultiPolygon features is promoted to multi before import, so the table's geometry column is MultiPolygon and both kinds fit. A source in EPSG 4326 is reprojected to the database's standard CRS before import, so every table shares one SRID. The column type and SRID are fixed at creation and hard to change later.Fix type and CRS before the table existsmixed single/multi→ native:promotetomulticolumn: MultiPolygonboth kinds fitforeign CRS→ native:reprojectlayercolumn: SRID 25832one SRID per database

from qgis.core import QgsCoordinateReferenceSystem

DB_CRS = QgsCoordinateReferenceSystem("EPSG:25832")

def prepare(layer):
    if layer.crs() != DB_CRS:
        layer = processing.run("native:reprojectlayer", {
            "INPUT": layer, "TARGET_CRS": DB_CRS, "OUTPUT": "memory:"})["OUTPUT"]
    layer = processing.run("native:promotetomulti", {
        "INPUT": layer, "OUTPUT": "memory:"})["OUTPUT"]
    return layer

parcels = prepare(QgsVectorLayer("/data/inbox/parcels.shp", "parcels", "ogr"))
processing.run("native:importintopostgis", {
    "INPUT": parcels, "DATABASE": "assets_db", "SCHEMA": "cadastre",
    "TABLENAME": "parcels", "PRIMARY_KEY": "parcel_pk", "GEOMETRY_COLUMN": "geom",
    "OVERWRITE": True, "CREATEINDEX": True, "LOWERCASE_NAMES": True})

Breakdown: Keeping every table in one database in one CRS makes spatial SQL across tables straightforward — joins and intersections need matching SRIDs. Reprojecting during preparation, with a deliberately chosen transformation, is better than discovering mismatches in queries later; see batch reprojecting vector layers. Promoting to multi gives the column a type every feature fits; importing a shapefile that declares Polygon but contains multipolygons otherwise fails part-way through.

Import with the connections API

For more control — custom options, no Processing dependency, or importing from inside a plugin — the provider connections API exposes the same exporter directly.

from qgis.core import QgsProviderRegistry, QgsVectorLayerExporter

md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.findConnection("assets_db")
dest = f'{conn.uri()} table="water"."valves" (geom)'
options = {"overwrite": True, "lowercaseFieldNames": True}
error, message = QgsVectorLayerExporter.exportLayer(
    prepare(QgsVectorLayer("/data/inbox/valves.gpkg", "valves", "ogr")),
    dest, "postgres", DB_CRS, False, options)
print("ok" if error == QgsVectorLayerExporter.NoError else message)

Breakdown: QgsVectorLayerExporter.exportLayer takes a layer, a destination URI, a provider key, the destination CRS and options, and returns an error code with a message. Building the destination from the connection's own URI plus a table="schema"."name" (geom) clause reuses the stored connection details. Options mirror the Processing parameters: overwrite, lowercaseFieldNames, dropStringConstraints, forceSinglePartGeometryType. The connections API also runs SQL, lists schemas and tables, and drops or renames tables — all covered in using the database connections API.

Index, analyze and comment

A freshly imported table has a spatial index if requested, but PostgreSQL's planner knows nothing about its contents until statistics are collected. A few statements after import make queries fast and the table self-describing.

conn.executeSql("""
    CREATE INDEX IF NOT EXISTS hydrants_geom_idx ON water.hydrants USING gist (geom);
    CREATE UNIQUE INDEX IF NOT EXISTS hydrants_no_idx ON water.hydrants (hydrant_no);
    ANALYZE water.hydrants;
    COMMENT ON TABLE water.hydrants IS 'Hydrants, imported 2026-10-02 from hydrants.gpkg (survey 2026)';
""")

Breakdown: The spatial index is created only if missing, so the statement is safe to repeat. A unique index on the business identifier enforces it in the database, where every client respects it. ANALYZE collects statistics so the planner chooses index scans for selective queries. A table comment records where the data came from — PostgreSQL tools and QGIS's browser both show it, so provenance travels with the table.

Import a folder of files

Bulk imports benefit from one function that applies the same naming, preparation and post-processing to every file, and a log of what happened.

Folder to schemaEach file in a folder becomes a table in one schema. File names are normalised into valid table names. Each layer is prepared, imported, indexed and analysed. Failures are logged with their message and do not stop the rest. A summary lists imported tables and row counts.One function, every file, one logfolder*.shp, *.gpkgper filename → prepareimport → indexschema + logtables, countsfailures

import re
from pathlib import Path

def table_name(stem):
    return re.sub(r"[^a-z0-9_]+", "_", stem.lower()).strip("_")[:63]

log = []
for path in sorted(Path("/data/inbox/2026_delivery").glob("*.shp")):
    name = table_name(path.stem)
    try:
        lyr = prepare(QgsVectorLayer(str(path), name, "ogr"))
        processing.run("native:importintopostgis", {
            "INPUT": lyr, "DATABASE": "assets_db", "SCHEMA": "delivery_2026",
            "TABLENAME": name, "PRIMARY_KEY": "pk", "GEOMETRY_COLUMN": "geom",
            "OVERWRITE": True, "CREATEINDEX": True, "LOWERCASE_NAMES": True})
        conn.executeSql(f"ANALYZE delivery_2026.{name};")
        log.append((name, lyr.featureCount(), "ok"))
    except Exception as err:
        log.append((name, 0, f"FAILED: {err}"))
for row in log:
    print(*row)

Breakdown: Normalising file names into lower-case, underscore-separated identifiers of at most 63 characters — PostgreSQL's limit — avoids quoting and truncation problems. Catching exceptions per file means one bad shapefile does not abort the whole delivery; the log names it and the reason. Importing into a dedicated schema per delivery keeps raw imports separate from curated tables, which can then be built with SQL from them.

Grant access and register the table

A table nobody else can read is not yet shared. Most databases separate the role that loads data from the roles that read and edit it, so an import script should finish by granting the right privileges — and, where an organisation keeps a catalogue of datasets, recording the new table there.

conn.executeSql("""
    GRANT USAGE ON SCHEMA water TO gis_readers, gis_editors;
    GRANT SELECT ON water.hydrants TO gis_readers;
    GRANT SELECT, INSERT, UPDATE, DELETE ON water.hydrants TO gis_editors;
    GRANT USAGE ON SEQUENCE water.hydrants_hydrant_pk_seq TO gis_editors;
""")

Breakdown: Readers need USAGE on the schema and SELECT on the table; editors also need write privileges and use of the primary key's sequence, without which inserts from QGIS fail with a permission error that names the sequence rather than the table. Granting to group roles rather than individual users keeps permissions manageable as people join and leave. The sequence name follows PostgreSQL's convention table_column_seq for serial columns; check it with \d water.hydrants in psql if your import named the key differently.

Verify the import

Counts, extents and a sample of values should match between source and table.

from qgis.core import QgsDataSourceUri

check_uri = QgsDataSourceUri(conn.uri())
check_uri.setDataSource("water", "hydrants", "geom", "", "hydrant_pk")
table = QgsVectorLayer(check_uri.uri(False), "check", "postgres")
assert table.isValid(), "table not readable"
assert table.featureCount() == src.featureCount(), "row count differs"
print(table.crs().authid(), table.wkbType(), table.extent().toString(0))

Breakdown: Loading the new table as a layer through the same connection confirms that QGIS can read it with the intended key and geometry column. Matching feature counts catch features rejected for geometry type. Comparing the extent with the source's (after any reprojection) catches CRS mistakes. A spot check of a few attribute values by key catches encoding problems with non-ASCII text, which are otherwise discovered by users months later.

QGIS version compatibility

native:importintopostgis replaced qgis:importintopostgis in QGIS 3.24 and is available on 3.34 LTR, 3.40 LTR and QGIS 4 with the parameters shown. The connections API, QgsVectorLayerExporter and executeSql are available on all these releases. On QGIS 4, QgsVectorLayerExporter.NoError is Qgis.VectorExportResult.Success.

Troubleshooting

  • The import fails partway. Mixed single and multipart geometry; promote to multi first.
  • Columns have quoted mixed-case names. Lower-case names during import.
  • Spatial queries ignore the index. Run ANALYZE, and make sure queries use the same SRID as the column.
  • Accented characters are garbled. The source encoding was wrong; set ENCODING or fix the shapefile's .cpg.

Conclusion

Prepare layers — one CRS for the database, multi geometry types — import with native:importintopostgis or the exporter through a stored connection, lower-case names, create spatial and key indexes, analyze and comment tables, import folders with normalised names and a log, and verify counts, CRS and extents.

Frequently Asked Questions

Is ogr2ogr faster for huge imports? Often yes, especially with -gt for large transactions. The PyQGIS route is more convenient when data is already loaded or prepared in QGIS.

Can I import into an existing table? Yes, without overwrite — but columns must match. For routine appends see appending features.

How do I import a CSV without geometry? Import it the same way; a layer without geometry becomes a plain table.

Should the primary key be the business id? Keep a surrogate serial key for the database and a separate unique business id for people.