How to Load and Write PostGIS Layers from PyQGIS

Problem statement

In the QGIS GUI, PostGIS is a saved connection and a browser tree: double-click a table and it loads. From a script none of that exists, and the first attempt returns an invalid layer with no explanation:

layer = QgsVectorLayer("postgresql://[email protected]/gis?table=parcels", "parcels", "postgres")
print(layer.isValid())          # False

The provider needs a specific URI format, not a libpq URL. And once the layer loads, the questions multiply: how do you write results back, how do you run server-side SQL, how do you avoid pulling 12 million rows across the network, and where does the password live?

Common failure points:

a hand-built connection string in the wrong format
no primary key on a view, so the provider refuses to load it
the geometry column or its type left unspecified, so the layer loads empty
credentials embedded in the script, or relying on a GUI-saved connection that a script cannot see
reading a whole table when a WHERE clause would have returned 400 rows

Quick answer

Build the URI with QgsDataSourceUri, and always check isValid() plus the provider message:

set the connection with setConnection() — never string-concatenate
set the data source with schema, table, geometry column, and a key column
add a WHERE clause so the server does the filtering
check isValid() and read dataProvider().error().message() on failure
take credentials from the environment, not from the code

import os
from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection(
    os.environ.get("PGHOST", "localhost"),
    os.environ.get("PGPORT", "5432"),
    os.environ["PGDATABASE"],
    os.environ["PGUSER"],
    os.environ["PGPASSWORD"],
)
uri.setDataSource("public", "parcels", "geom", aKeyColumn="gid")
uri.setSrid("27700")
uri.setWkbType(6)                       # QgsWkbTypes.MultiPolygon — optional but faster
uri.setSql("class = 'residential' AND area_m2 > 500")

layer = QgsVectorLayer(uri.uri(False), "parcels", "postgres")
if not layer.isValid():
    raise RuntimeError(layer.dataProvider().error().message())

print(f"{layer.featureCount():,} features, {layer.crs().authid()}")

uri.uri(False) returns the URI without the password expanded, which is the form to log or store in a project. Passing True includes the password and should never reach a file or a log line.

Anatomy of a PostGIS URI

Anatomy of a PostGIS data source URI: connection, schema, table, geometry column, key, filter. — Six parts — omitting the key column or the geometry column is what makes a layer invalid or empty.

Step-by-step solution

Vertical steps: build URI, add filter, validate layer, read features, write results, verify. — Six steps — filtering before loading is what keeps a 12-million-row table usable.

Build the URI properly

from qgis.core import QgsDataSourceUri

uri = QgsDataSourceUri()
uri.setConnection("db.internal", "5432", "gis", "gis_user", "secret")
uri.setDataSource(
    aSchema="public",
    aTable="parcels",
    aGeometryColumn="geom",
    aSql="",                       # a WHERE clause, added below
    aKeyColumn="gid",              # a unique integer column — required for views
)
uri.setSrid("27700")
uri.setUseEstimatedMetadata(True)  # skip a full table scan for extent and type
print(uri.uri(False))

Two options are worth setting explicitly on large tables. setUseEstimatedMetadata(True) tells the provider to use PostgreSQL statistics rather than scanning the table for its extent and geometry type, which turns a minute of waiting into milliseconds. setWkbType() skips the type probe entirely when you already know it.

For SSL and other libpq parameters, set them on the URI:

uri.setParam("sslmode", "require")
uri.setParam("connect_timeout", "10")
uri.setParam("application_name", "gis-pipeline")     # shows up in pg_stat_activity

application_name is a small kindness to whoever administers the database: your pipeline's connections become identifiable.

Filter on the server, not in Python

uri.setSql("survey_date >= '2026-01-01' AND class IN ('residential', 'commercial')")
layer = QgsVectorLayer(uri.uri(False), "recent parcels", "postgres")
print(layer.featureCount())

For anything more complex, load the result of a query as a layer:

from qgis.core import QgsDataSourceUri, QgsVectorLayer

query = """(
    SELECT p.gid, p.class, p.geom, z.zone_name
    FROM parcels p
    JOIN zones z ON ST_Intersects(p.geom, z.geom)
    WHERE p.area_m2 > 1000
)"""

uri = QgsDataSourceUri()
uri.setConnection("db.internal", "5432", "gis", "gis_user", "secret")
uri.setDataSource("", query, "geom", "", "gid")     # empty schema, query as the table
layer = QgsVectorLayer(uri.uri(False), "parcels in zones", "postgres")
print(layer.isValid(), layer.featureCount())

The parentheses around the query are required, and the gid key column must be unique across the result — a join that duplicates rows will produce a layer that behaves erratically.

Restricting to the current map extent avoids downloading the country to look at a town:

from qgis.core import QgsFeatureRequest, QgsRectangle

request = QgsFeatureRequest(QgsRectangle(320000, 670000, 340000, 690000))
request.setSubsetOfAttributes(["gid", "class"], layer.fields())
for feature in layer.getFeatures(request):
    ...

Diagnose an invalid layer

layer = QgsVectorLayer(uri.uri(False), "parcels", "postgres")
if not layer.isValid():
    provider = layer.dataProvider()
    print("error   :", provider.error().message() if provider else "no provider created")
    print("uri     :", uri.uri(False))
    print("has key :", bool(uri.keyColumn()))
    print("geom col:", uri.geometryColumn())

The provider's message is specific and worth reading: authentication failed is credentials, relation does not exist is schema or search path, no suitable key column means a view without a unique integer column, and a timeout is usually network or sslmode.

For a view, supply the key explicitly:

uri.setDataSource("public", "v_parcels_summary", "geom", "", "row_id")

Write results back to PostGIS

from qgis.core import (QgsVectorLayerExporter, QgsVectorFileWriter,
                       QgsCoordinateTransformContext, QgsDataSourceUri)

out_uri = QgsDataSourceUri()
out_uri.setConnection("db.internal", "5432", "gis", "gis_user", "secret")
out_uri.setDataSource("results", "parcels_buffered", "geom")

error, message = QgsVectorLayerExporter.exportLayer(
    layer, out_uri.uri(False), "postgres",
    layer.crs(), False,                       # False = do not overwrite
    {"overwrite": True, "createIndex": True},
)
if error != QgsVectorLayerExporter.NoError:
    raise RuntimeError(f"export failed: {message}")
print("written to results.parcels_buffered")

The Processing algorithm is often simpler, and it is what the GUI's "Export to PostgreSQL" uses:

import processing

processing.run("native:importintopostgis", {
    "INPUT": layer,
    "DATABASE": "gis_connection",        # a *named* connection, see below
    "SCHEMA": "results",
    "TABLENAME": "parcels_buffered",
    "PRIMARY_KEY": "gid",
    "GEOMETRY_COLUMN": "geom",
    "OVERWRITE": True,
    "CREATEINDEX": True,
    "LOWERCASE_NAMES": True,
    "DROP_STRING_LENGTH": False,
    "FORCE_SINGLETYPE": False,
})

Note that this algorithm needs a named connection stored in QGIS settings — which a fresh headless profile does not have. Create it in the script:

from qgis.core import QgsProviderRegistry, QgsProviderConnectionException

metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
conn_uri = uri.uri(False)
try:
    metadata.saveConnection(metadata.createConnection(conn_uri, {}), "gis_connection")
except QgsProviderConnectionException as exc:
    print("could not register the connection:", exc)

Run SQL directly through the provider connection API

For DDL, indexes and maintenance you do not need a layer at all.

from qgis.core import QgsProviderRegistry

metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = metadata.createConnection(uri.uri(False), {})

print("schemas:", conn.schemas())
print("tables :", [t.tableName() for t in conn.tables("public")][:10])

conn.executeSql("CREATE SCHEMA IF NOT EXISTS results")
conn.executeSql("""
    CREATE INDEX IF NOT EXISTS parcels_buffered_geom_idx
    ON results.parcels_buffered USING GIST (geom)
""")
rows = conn.executeSql("SELECT class, count(*) FROM public.parcels GROUP BY class")
for cls, n in rows:
    print(f"{cls:<14} {n:>8,}")

This is the same API the QGIS Browser uses, so it works consistently across providers that support it.

Keep credentials out of the code and the project

import os
from qgis.core import QgsDataSourceUri

def connection_uri() -> QgsDataSourceUri:
    uri = QgsDataSourceUri()
    uri.setConnection(
        os.environ.get("PGHOST", "localhost"),
        os.environ.get("PGPORT", "5432"),
        os.environ["PGDATABASE"],
        os.environ["PGUSER"],
        os.environ["PGPASSWORD"],
    )
    uri.setParam("application_name", "gis-pipeline")
    return uri

When the layer goes into a saved project, strip the credentials so they are not written into the .qgz:

save_uri = QgsDataSourceUri(uri.uri(False))
save_uri.setPassword("")
save_uri.setUsername("")                 # the user is then prompted, or libpq env vars apply
layer.setDataSource(save_uri.uri(False), layer.name(), "postgres")

A project file containing a password is a credential leak that travels by email, which is exactly the kind that goes unnoticed for years.

Code examples

Example 1: a reusable PostGIS helper

"""pg.py — load and write PostGIS layers from PyQGIS, safely."""
import os
from qgis.core import (QgsDataSourceUri, QgsVectorLayer, QgsVectorLayerExporter,
                       QgsProviderRegistry)

def base_uri() -> QgsDataSourceUri:
    uri = QgsDataSourceUri()
    uri.setConnection(os.environ.get("PGHOST", "localhost"),
                      os.environ.get("PGPORT", "5432"),
                      os.environ["PGDATABASE"],
                      os.environ["PGUSER"],
                      os.environ["PGPASSWORD"])
    uri.setParam("application_name", "gis-pipeline")
    uri.setParam("connect_timeout", "10")
    return uri

def load_table(table: str, schema="public", geom="geom", key="gid",
               where: str | None = None, name: str | None = None,
               estimated=True) -> QgsVectorLayer:
    uri = base_uri()
    uri.setDataSource(schema, table, geom, where or "", key)
    uri.setUseEstimatedMetadata(estimated)
    layer = QgsVectorLayer(uri.uri(False), name or f"{schema}.{table}", "postgres")
    if not layer.isValid():
        raise RuntimeError(
            f"{schema}.{table} did not load — "
            f"{layer.dataProvider().error().message() if layer.dataProvider() else 'no provider'}"
        )
    return layer

def load_query(sql: str, key="gid", geom="geom", name="query") -> QgsVectorLayer:
    uri = base_uri()
    uri.setDataSource("", f"({sql})", geom, "", key)
    layer = QgsVectorLayer(uri.uri(False), name, "postgres")
    if not layer.isValid():
        raise RuntimeError(f"query layer invalid: {layer.dataProvider().error().message()}")
    return layer

def write_layer(layer, table: str, schema="results", overwrite=True) -> None:
    uri = base_uri()
    uri.setDataSource(schema, table, "geom")
    error, message = QgsVectorLayerExporter.exportLayer(
        layer, uri.uri(False), "postgres", layer.crs(), False,
        {"overwrite": overwrite, "createIndex": True, "lowercaseFieldNames": True})
    if error != QgsVectorLayerExporter.NoError:
        raise RuntimeError(f"export to {schema}.{table} failed: {message}")

def execute(sql: str):
    metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
    conn = metadata.createConnection(base_uri().uri(False), {})
    return conn.executeSql(sql)

parcels = load_table("parcels", where="class = 'residential'")
print(f"{parcels.featureCount():,} residential parcels")

import processing
buffered = processing.run("native:buffer", {
    "INPUT": parcels, "DISTANCE": 25, "OUTPUT": "TEMPORARY_OUTPUT"})["OUTPUT"]

execute("CREATE SCHEMA IF NOT EXISTS results")
write_layer(buffered, "parcels_buffered_25m")
print(execute("SELECT count(*) FROM results.parcels_buffered_25m"))

Example 2: process by tile so nothing is loaded whole

from qgis.core import QgsRectangle
import processing

bounds = execute("SELECT ST_XMin(e), ST_YMin(e), ST_XMax(e), ST_YMax(e) "
                 "FROM (SELECT ST_Extent(geom) e FROM public.parcels) s")[0]
minx, miny, maxx, maxy = map(float, bounds)

NX = NY = 4
dx, dy = (maxx - minx) / NX, (maxy - miny) / NY

for i in range(NX):
    for j in range(NY):
        x0, y0 = minx + i*dx, miny + j*dy
        where = (f"geom && ST_MakeEnvelope({x0}, {y0}, {x0+dx}, {y0+dy}, 27700)")
        tile = load_table("parcels", where=where, name=f"tile_{i}{j}")
        if tile.featureCount() == 0:
            continue
        result = processing.run("native:buffer",
                                {"INPUT": tile, "DISTANCE": 25, "OUTPUT": "TEMPORARY_OUTPUT"})
        write_layer(result["OUTPUT"], "parcels_buffered_25m", overwrite=(i == 0 and j == 0))
        print(f"tile {i},{j}: {tile.featureCount():,} features", flush=True)

The && operator uses the GIST index, so each tile query is fast even on a very large table.

Example 3: keep credentials out of a saved project

from qgis.core import QgsProject, QgsDataSourceUri

project = QgsProject.instance()
parcels = load_table("parcels")
project.addMapLayer(parcels)

# strip credentials before writing the project
for layer in project.mapLayers().values():
    if layer.providerType() != "postgres":
        continue
    stripped = QgsDataSourceUri(layer.source())
    stripped.setPassword("")
    layer.setDataSource(stripped.uri(False), layer.name(), "postgres")

project.write("data/out/postgis_project.qgz")

# confirm nothing leaked
text = open("data/out/postgis_project.qgz", "rb").read()
assert os.environ["PGPASSWORD"].encode() not in text, "password leaked into the project!"

Example 4: use PostGIS for the heavy lifting

Sometimes the right answer is not to move the data at all.

rows = execute("""
    SELECT z.zone_name,
           count(p.gid)                AS parcels,
           round(sum(ST_Area(p.geom))::numeric / 10000, 2) AS area_ha
    FROM public.zones z
    LEFT JOIN public.parcels p ON ST_Intersects(z.geom, p.geom)
    GROUP BY z.zone_name
    ORDER BY area_ha DESC
""")
for zone, parcels, area in rows[:10]:
    print(f"{zone:<24} {parcels:>7,} parcels  {area:>10} ha")

A spatial join across millions of rows runs on the server, using its indexes, and returns a few dozen rows. Doing the same in Python would mean transferring both tables across the network first.

Explanation

The PostGIS provider is a database client, and a QgsVectorLayer over it is a live view rather than a copy. Features are fetched lazily as the canvas or an iterator asks for them, which is why a layer over a 40-million-row table opens instantly and why a poorly filtered layer can then take minutes to draw.

Grid comparing QgsVectorLayerExporter, native:importintopostgis, ogr2ogr and to_postgis for writing. — Four ways to write back — the right one depends on whether you are in QGIS or in pandas.

The URI is where most problems live because it carries more than a connection. It names the schema, the table, the geometry column, and a key column — and each omission has its own symptom. Without the geometry column the provider may pick the wrong one or load nothing; without a unique integer key it refuses to load views at all, because it needs a stable identifier to fetch and edit rows. QgsDataSourceUri exists so you do not have to know the escaping rules for any of that.

Two performance settings deserve to be habits on large tables. setUseEstimatedMetadata(True) uses PostgreSQL's statistics for the layer extent and geometry type instead of scanning; without it, opening a large table means a full ST_Extent over every row. And a WHERE clause on the URI is evaluated by the server, so the rows never cross the network — always preferable to filtering a GeoDataFrame after the fact.

Writing has several routes and they are not equivalent. QgsVectorLayerExporter is the general one and works with any URI. native:importintopostgis is what the GUI uses, and it needs a named connection registered in the QGIS profile — which is why it fails in a headless container until you create one. ogr2ogr is excellent for bulk loads and can be driven with subprocess. And if you are already in pandas, GeoDataFrame.to_postgis() bypasses QGIS entirely.

Finally, credentials. QgsDataSourceUri.uri(True) expands the password, and anything you write that string into — a project file, a log, a run record — now contains it. Reading credentials from the environment, using uri(False), and stripping the password before saving a project are three small habits that keep a database password out of the files that get emailed around.

Edge cases or notes

Views need an explicit key column: Add a unique integer column (row_number() OVER () works) and pass it as aKeyColumn, or the layer will not load.
Query layers need surrounding parentheses: setDataSource("", "(SELECT …)", "geom", "", "gid"). Without them the provider treats the SQL as a table name.
Estimated metadata can be stale: If ANALYZE has not run recently, the extent may be wrong. Turn it off when exact bounds matter.
Mixed geometry types in one column: Set setWkbType() or the provider probes, which is slow and can surprise you. Consider a typed view.
search_path matters: An unqualified table name resolves against the role's search path. Always give the schema explicitly.
Long-running transactions block: A layer left in edit mode holds a transaction open. Commit or roll back promptly in scripts.
native:importintopostgis needs a saved connection: Register one with the provider metadata API in headless environments.

Internal links

FAQ

Why is my PostGIS layer invalid?

Read layer.dataProvider().error().message() — it names the cause. The usual ones are wrong credentials, a table that needs its schema qualified, a view without a key column, or a missing geometry column in the URI.

How do I build the connection string?

With QgsDataSourceUri: setConnection() for host, port, database, user and password, then setDataSource() for schema, table, geometry column and key column. Hand-built strings get the escaping wrong.

How do I load only some rows?

Pass a WHERE clause as the URI's SQL, or use a query layer. Either way the filtering happens on the server, so the excluded rows never cross the network.

Why is opening a large table so slow?

The provider is scanning for the extent and geometry type. Call uri.setUseEstimatedMetadata(True) to use PostgreSQL statistics instead, and set setWkbType() if you know the type.

How do I write a result back to PostGIS?

QgsVectorLayerExporter.exportLayer() with a destination URI, or the native:importintopostgis algorithm if you have a named connection registered. From pandas, GeoDataFrame.to_postgis() is simpler.

How do I stop the password ending up in the project file?

Strip it before saving: copy the URI, setPassword(""), and call layer.setDataSource() with the result. Read the real credentials from the environment at run time.

Can I run plain SQL from PyQGIS?

Yes — get the provider connection with QgsProviderRegistry.instance().providerMetadata("postgres").createConnection(uri, {}) and call executeSql(). It is the same API the Browser uses, and it is the right tool for DDL and indexes.

How to Load and Write PostGIS Layers from PyQGIS #

Problem statement #

Quick answer #

Anatomy of a PostGIS URI #

Step-by-step solution #

Build the URI properly #

Filter on the server, not in Python #

Diagnose an invalid layer #

Write results back to PostGIS #

Run SQL directly through the provider connection API #

Keep credentials out of the code and the project #

Code examples #

Example 1: a reusable PostGIS helper #

Example 2: process by tile so nothing is loaded whole #

Example 3: keep credentials out of a saved project #

Example 4: use PostGIS for the heavy lifting #

Explanation #

Edge cases or notes #

Internal links #

FAQ #

Why is my PostGIS layer invalid? #

How do I build the connection string? #

How do I load only some rows? #

Why is opening a large table so slow? #

How do I write a result back to PostGIS? #

How do I stop the password ending up in the project file? #

Can I run plain SQL from PyQGIS? #