How to Load and Write PostGIS Layers from PyQGIS

On this page

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://user@localhost:55433/gis?table=rutland_parcels", "parcels", "postgres")
print(layer.isValid())

False

The provider needs its own 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 a whole table across the network, and where does the password live?

Common failure points:

a hand-built connection string in the wrong format
no key column on a view, so the provider refuses to load it
a query layer without its surrounding parentheses
credentials embedded in the URI — and from there in every project file and log line
reading a whole table when a WHERE clause would have returned a few hundred rows

The examples run under QGIS 4.2 against a PostGIS 3.4 database holding HM Land Registry's 21,253 Rutland parcels and the county's 15 wards.

Quick answer

Build the URI with QgsDataSourceUri, check isValid(), and keep the password out of the URI:

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 the QGIS message log on failure
leave user and password empty and let libpq take them from PGUSER/PGPASSWORD, a .pgpass file or a service file

import os
from qgis.core import QgsDataSourceUri, QgsVectorLayer, Qgis

uri = QgsDataSourceUri()
uri.setConnection(
    os.environ.get("PGHOST", "localhost"),
    os.environ.get("PGPORT", "5432"),
    os.environ["PGDATABASE"],
    "",                                  # user: from PGUSER / .pgpass
    "",                                  # password: from PGPASSWORD / .pgpass
)
uri.setDataSource("public", "rutland_parcels", "geom", aKeyColumn="gid")
uri.setSrid("27700")
uri.setWkbType(Qgis.WkbType.Polygon)     # optional: skips the type probe
uri.setSql("ST_Area(geom) > 100000")     # evaluated by PostgreSQL

layer = QgsVectorLayer(uri.uri(False), "large parcels", "postgres")
if not layer.isValid():
    raise RuntimeError(f"could not load {uri.uri(False)}")

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

926 features, EPSG:27700
dbname='gis' host=localhost port=55433 key='gid' srid=27700 type=Polygon table="public"."rutland_parcels" (geom) sql=ST_Area(geom) > 100000

Look at what uri.uri(False) printed: there is no password in it because none was put in. The False does not remove one. Its parameter is expandAuthConfig — whether to inline credentials held in QGIS's encrypted authentication database — and a password set with setConnection() is in the string either way:

leaky = QgsDataSourceUri(uri.uri(False))
leaky.setUsername("user")
leaky.setPassword("guide")
print("uri(False):", leaky.uri(False).split(" key=")[0])
print("uri(True): ", leaky.uri(True).split(" key=")[0])

uri(False): dbname='gis' host=localhost port=55433 user='user' password='guide'
uri(True):  dbname='gis' host=localhost port=55433 user='user' password='guide'

Anatomy of a PostGIS URI

Six parts — omitting the key column or the geometry column is what makes a layer invalid or empty.

URIs, server-side filters and writing back

Six steps — filtering before loading is what keeps a 12-million-row table usable.

Build the URI properly

base = QgsDataSourceUri()
base.setConnection(os.environ.get("PGHOST", "localhost"), os.environ.get("PGPORT", "5432"),
                   os.environ["PGDATABASE"], "", "")
base.setDataSource(
    aSchema="public",
    aTable="rutland_parcels",
    aGeometryColumn="geom",
    aSql="",                       # a WHERE clause, added below
    aKeyColumn="gid",              # a unique integer column — required for views
)
base.setSrid("27700")
base.setUseEstimatedMetadata(True) # read extent and type from table statistics
base.setParam("application_name", "gis-pipeline")      # shows up in pg_stat_activity
base.setParam("connect_timeout", "10")
print(base.uri(False))
parcels = QgsVectorLayer(base.uri(False), "parcels", "postgres")
print(parcels.isValid(), f"{parcels.featureCount():,}", parcels.extent().toString(0))

dbname='gis' host=localhost port=55433 key='gid' estimatedmetadata=true srid=27700 application_name='gis-pipeline' connect_timeout='10' table="public"."rutland_parcels" (geom)
True -1 479001,292381 : 506878,319201

setUseEstimatedMetadata(True) tells the provider to use PostgreSQL's statistics rather than scanning the table for its extent and geometry type — a real saving on tens of millions of rows, and only as current as the last ANALYZE. It also changes what featureCount() means, as the next step shows. setWkbType() skips the type probe entirely when you already know it. Other libpq parameters, such as sslmode, go through setParam() the same way; application_name is a small kindness to whoever administers the database.

Filter on the server, not in Python

filtered = QgsDataSourceUri(base.uri(False))
filtered.setSql("ST_Area(geom) > 100000")
estimated = QgsVectorLayer(filtered.uri(False), "over 10 ha", "postgres")
filtered.setUseEstimatedMetadata(False)
exact = QgsVectorLayer(filtered.uri(False), "over 10 ha", "postgres")
print("featureCount(), estimated metadata:", estimated.featureCount())
print("featureCount(), exact:             ", exact.featureCount())
print("features actually returned:        ", sum(1 for _ in estimated.getFeatures()))

featureCount(), estimated metadata: 61440
featureCount(), exact:              926
features actually returned:         926

With estimated metadata on, featureCount() is PostgreSQL's planner estimate for the filtered query — here more rows than the whole table holds, for a filter that matches 926. Iterating returns the right features; only the count is invented. Count with estimated metadata off, or with SELECT count(*), whenever the number matters.

For anything more complex, load the result of a query as a layer — here the large parcels with the ward each one falls in:

query = """(
    SELECT p.gid, p.geom, w.wd24nm AS ward
    FROM rutland_parcels p
    JOIN rutland_wards w ON ST_Within(ST_PointOnSurface(p.geom), w.geom)
    WHERE ST_Area(p.geom) > 100000
)"""

joined_uri = QgsDataSourceUri(base.uri(False))
joined_uri.setDataSource("", query, "geom", "", "gid")     # empty schema, query as the table
joined = QgsVectorLayer(joined_uri.uri(False), "large parcels by ward", "postgres")
print(joined.isValid(), joined.featureCount(), joined.fields().names())

no_parens = QgsDataSourceUri(base.uri(False))
no_parens.setDataSource("", query.strip()[1:-1], "geom", "", "gid")
with captured_messages() as messages:
    print("without the parentheses:", QgsVectorLayer(no_parens.uri(False), "q", "postgres").isValid())
print(messages[0][:90])

True 848 ['gid', 'ward']
without the parentheses: False
[PostGIS] Erroneous query: SELECT has_table_privilege('"
    SELECT p.gid, p.geom, w.wd24n

The parentheses are required — without them the provider treats the SQL as a table name — and the key column must be unique across the result; a join that duplicates rows produces a layer that behaves erratically.

Restricting to an extent avoids downloading the county to look at a town:

from qgis.core import QgsFeatureRequest, QgsRectangle

request = QgsFeatureRequest(QgsRectangle(484000, 307500, 487000, 310000))       # Oakham
request.setSubsetOfAttributes(["gid"], parcels.fields())
print(sum(1 for _ in parcels.getFeatures(request)), "parcels in the Oakham window")

5703 parcels in the Oakham window

Measured above under QGIS 4.2 on PostGIS 3.4: the estimate is the planner's row estimate for the filtered query.

Diagnose an invalid layer

layer.error() and the provider's error() are empty for PostGIS failures on QGIS 4.2; the reason is in the message log, which captured_messages() (defined with the data) reads. A view is the classic case:

view_uri = QgsDataSourceUri(base.uri(False))
view_uri.setDataSource("public", "v_large_parcels", "geom", "", "")          # no key column
with captured_messages() as messages:
    view = QgsVectorLayer(view_uri.uri(False), "view", "postgres")
print("view, no key:", view.isValid(), messages[:2])

view_uri.setDataSource("public", "v_large_parcels", "geom", "", "gid")
print("view, key gid:", QgsVectorLayer(view_uri.uri(False), "view", "postgres").isValid())

view, no key: False ['[PostGIS] No key field for view/query given.', '[PostGIS] PostgreSQL layer has no primary key.']
view, key gid: True

The other messages are as specific: password authentication failed is credentials, Connection refused is host or port, an erroneous query naming the relation is schema or table. PyQGIS layer fails to load shows each of them.

Write results back to PostGIS

import processing
from qgis.core import QgsVectorLayerExporter

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

out_uri = QgsDataSourceUri(base.uri(False))
out_uri.setDataSource("results", "large_parcels_buffered", "geom")
execute("CREATE SCHEMA IF NOT EXISTS results")

result, message = QgsVectorLayerExporter.exportLayer(
    buffered, out_uri.uri(False), "postgres", buffered.crs(), False, {"overwrite": True},
)
if result != Qgis.VectorExportResult.Success:
    raise RuntimeError(f"export failed: {message}")
print(result.name, execute("SELECT count(*), GeometryType(geom) FROM results.large_parcels_buffered GROUP BY 2"))
print("indexes:", execute("SELECT indexname FROM pg_indexes WHERE tablename = 'large_parcels_buffered'"))

Success [[848, 'MULTIPOLYGON']]
indexes: [['large_parcels_buffered_pkey']]

exportLayer returns a Qgis.VectorExportResult and a message rather than raising. It created the table with a primary key and no spatial index, so add one:

execute("CREATE INDEX IF NOT EXISTS large_parcels_buffered_geom_idx "
        "ON results.large_parcels_buffered USING GIST (geom)")
print(execute("SELECT indexname FROM pg_indexes WHERE tablename = 'large_parcels_buffered' ORDER BY 1"))

[['large_parcels_buffered_geom_idx'], ['large_parcels_buffered_pkey']]

The Processing algorithm is what the GUI's "Export to PostgreSQL" uses, and it needs a named connection stored in the QGIS profile — which a fresh headless profile does not have:

from qgis.core import QgsProcessingException

params = {"INPUT": buffered, "DATABASE": "gis_connection", "SCHEMA": "results",
          "TABLENAME": "large_parcels_imported", "PRIMARY_KEY": "gid", "GEOMETRY_COLUMN": "geom",
          "OVERWRITE": True, "CREATEINDEX": True, "LOWERCASE_NAMES": True,
          "DROP_STRING_LENGTH": False, "FORCE_SINGLETYPE": False}
try:
    processing.run("native:importintopostgis", params)
except QgsProcessingException as error:
    print("no saved connection:", " ".join(str(error).split()))

metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
connection_uri = QgsDataSourceUri()
connection_uri.setConnection(os.environ.get("PGHOST", "localhost"), os.environ.get("PGPORT", "5432"),
                             os.environ["PGDATABASE"], os.environ["PGUSER"], "")
metadata.saveConnection(metadata.createConnection(connection_uri.uri(False), {"saveUsername": True}),
                        "gis_connection")
processing.run("native:importintopostgis", params)
print("imported:", execute("SELECT count(*) FROM results.large_parcels_imported"))
metadata.deleteConnection("gis_connection")            # it was written into the QGIS profile

no saved connection: Error exporting to PostGIS Connection to database failed
imported: [[848]]

saveConnection writes to the user's QGIS profile, where it persists after the script — so delete it, or use a profile of its own. Without saveUsername, the saved connection keeps only host, port and database, and libpq then connects as the operating-system user.

Run SQL directly through the provider connection API

For DDL, indexes and maintenance you do not need a layer at all. execute() below is a two-line wrapper around this:

conn = metadata.createConnection(base.uri(False), {})
print("schemas:", sorted(conn.schemas()))
print("tables :", sorted(t.tableName() for t in conn.tables("public") if t.tableName().startswith(("rutland", "v_"))))
rows = conn.executeSql("""
    SELECT w.wd24nm, count(*) FROM rutland_wards w
    JOIN rutland_parcels p ON ST_Within(ST_PointOnSurface(p.geom), w.geom)
    GROUP BY 1 ORDER BY 2 DESC LIMIT 3""")
for ward, n in rows:
    print(f"{ward:<28} {n:>6,}")

schemas: ['ogr_system_tables', 'public', 'results', 'tiger', 'tiger_data', 'topology']
tables : ['rutland_parcels', 'rutland_wards', 'v_large_parcels']
Uppingham                     2,053
Oakham South                  1,981
Braunston & Martinsthorpe     1,905

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

Both halves measured on this page under QGIS 4.2.

Keep credentials out of the code and the project

A layer whose URI carries a password writes that password into any project it is saved in:

import zipfile
from qgis.core import QgsProject

def project_contains(path, secret):
    with zipfile.ZipFile(path) as z:
        return any(secret.encode() in z.read(name) for name in z.namelist())

project = QgsProject.instance()
with_password = QgsDataSourceUri(base.uri(False))
with_password.setUsername(os.environ["PGUSER"])
with_password.setPassword(os.environ["PGPASSWORD"])
project.addMapLayer(QgsVectorLayer(with_password.uri(False), "parcels with password", "postgres"))
project.write("out/with_password.qgz")
print("password in the saved project:", project_contains("out/with_password.qgz", os.environ["PGPASSWORD"]))

project.clear()
project.addMapLayer(QgsVectorLayer(base.uri(False), "parcels", "postgres"))
project.write("out/from_environment.qgz")
print("password in the saved project:", project_contains("out/from_environment.qgz", os.environ["PGPASSWORD"]))
project.clear()

password in the saved project: True
password in the saved project: False

A project file containing a password is a credential leak that travels by email, which is exactly the kind that goes unnoticed for years. Stripping the password from a layer's source before saving works too, but the layer can no longer connect in that session unless the environment supplies the credentials — so put them in the environment in the first place.

Rutland's parcels and wards in PostGIS

The data used on this page

HM Land Registry's INSPIRE Index Polygons for Rutland (Open Government Licence, © Crown copyright and database rights) and Rutland's wards from the ONS Wards (December 2024) boundaries, loaded with GDAL into a throwaway PostGIS 3.4 in Docker on port 55433 as rutland_parcels (key gid) and rutland_wards, plus a view v_large_parcels of the parcels over 10 ha. The connection details go into the environment the way a scheduler or a .env file would put them there. captured_messages() collects what QGIS logs during a block; execute() runs SQL through the provider connection API.

import os
import time
from contextlib import contextmanager
from pathlib import Path

import requests
from osgeo import gdal

gdal.UseExceptions()
Path("data").mkdir(exist_ok=True)
Path("out").mkdir(exist_ok=True)
os.environ.update({"PGHOST": "localhost", "PGPORT": "55433", "PGDATABASE": "gis",
                   "PGUSER": "user", "PGPASSWORD": "guide"})           # a throwaway container's credentials

if not Path("data/parcels.gpkg").exists():
    reply = requests.Session().get(
        "https://use-land-property-data.service.gov.uk/datasets/inspire/download/Rutland.zip", timeout=300)
    reply.raise_for_status()
    Path("data/Rutland.zip").write_bytes(reply.content)
    gdal.VectorTranslate("data/parcels.gpkg", "/vsizip/data/Rutland.zip/Land_Registry_Cadastral_Parcels.gml",
                         layerName="parcels", selectFields=["INSPIREID"])
    Path("data/wards.geojson").write_bytes(requests.get(
        "https://services1.arcgis.com/ESMARspQHYMw9BZ9/arcgis/rest/services/"
        "Wards_December_2024_Boundaries_UK_BFC/FeatureServer/0/query",
        params={"where": "LAD24CD='E06000017'", "outFields": "WD24CD,WD24NM", "outSR": 27700, "f": "geojson"},
        timeout=120).content)

if os.system("docker inspect guide-postgis >/dev/null 2>&1") != 0:
    os.system("docker run -d --name guide-postgis -e POSTGRES_USER=user -e POSTGRES_PASSWORD=guide "
              "-e POSTGRES_DB=gis -p 127.0.0.1:55433:5432 postgis/postgis:16-3.4 >/dev/null")
    time.sleep(10)
pg = "PG:host=localhost port=55433 dbname=gis user=user password=guide"
for source, name in (("data/parcels.gpkg", "rutland_parcels"), ("data/wards.geojson", "rutland_wards")):
    gdal.VectorTranslate(pg, source, layerName=name, accessMode="overwrite",
                         layerCreationOptions=["GEOMETRY_NAME=geom", "FID=gid"])

os.environ.setdefault("QT_QPA_PLATFORM", "offscreen")
from qgis.core import QgsApplication, QgsDataSourceUri, QgsProviderRegistry, QgsVectorLayer, Qgis
qgs = QgsApplication([], False)
qgs.initQgis()
from processing.core.Processing import Processing
Processing.initialize()


@contextmanager
def captured_messages():
    messages = []
    def on_message(message, tag, level, text_format=None):
        messages.append(f"[{tag}] {message}")
    QgsApplication.messageLog().messageReceivedWithFormat.connect(on_message)
    try:
        yield messages
    finally:
        QgsApplication.processEvents()
        QgsApplication.messageLog().messageReceivedWithFormat.disconnect(on_message)


def execute(sql):
    admin = QgsDataSourceUri()
    admin.setConnection(os.environ["PGHOST"], os.environ["PGPORT"], os.environ["PGDATABASE"], "", "")
    return QgsProviderRegistry.instance().providerMetadata("postgres").createConnection(admin.uri(False), {}).executeSql(sql)


execute("DROP VIEW IF EXISTS v_large_parcels")
execute("CREATE VIEW v_large_parcels AS SELECT gid, inspireid, geom FROM rutland_parcels WHERE ST_Area(geom) > 100000")
print("QGIS", Qgis.QGIS_VERSION, "|", execute("SELECT count(*) FROM rutland_parcels")[0][0], "parcels,",
      execute("SELECT count(*) FROM rutland_wards")[0][0], "wards in PostGIS")

QGIS 4.2.2-Belém do Pará | 21253 parcels, 15 wards in PostGIS

Example 1: a reusable PostGIS helper

"""pg.py — load and write PostGIS layers from PyQGIS, with credentials from the environment."""
from qgis.core import QgsVectorLayerExporter

def base_uri() -> QgsDataSourceUri:
    uri = QgsDataSourceUri()
    uri.setConnection(os.environ.get("PGHOST", "localhost"), os.environ.get("PGPORT", "5432"),
                      os.environ["PGDATABASE"], "", "")                # user and password via libpq
    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)
    with captured_messages() as messages:
        layer = QgsVectorLayer(uri.uri(False), name or f"{schema}.{table}", "postgres")
    if not layer.isValid():
        raise RuntimeError(f"{schema}.{table} did not load — " + "; ".join(messages)[:300])
    return layer

def write_layer(layer, table: str, schema="results") -> None:
    uri = base_uri()
    uri.setDataSource(schema, table, "geom")
    result, message = QgsVectorLayerExporter.exportLayer(
        layer, uri.uri(False), "postgres", layer.crs(), False, {"overwrite": True, "lowercaseFieldNames": True})
    if result != Qgis.VectorExportResult.Success:
        raise RuntimeError(f"export to {schema}.{table} failed: {message.strip()}")
    execute(f"CREATE INDEX IF NOT EXISTS {table}_geom_idx ON {schema}.{table} USING GIST (geom)")

large = load_table("rutland_parcels", where="ST_Area(geom) > 100000", estimated=False)
print(f"{large.featureCount():,} parcels over 10 ha")
buffered = processing.run("native:buffer", {"INPUT": large, "DISTANCE": 25, "OUTPUT": "TEMPORARY_OUTPUT"})["OUTPUT"]
write_layer(buffered, "parcels_buffered_25m")
print(execute("SELECT count(*) FROM results.parcels_buffered_25m"))
try:
    load_table("rutland_parcel")
except RuntimeError as error:
    print("RuntimeError:", str(error)[:120])

926 parcels over 10 ha
[[926]]
RuntimeError: public.rutland_parcel did not load — [PostGIS] Erroneous query: SELECT has_table_privilege('"public"."rutland_parcel"','

Example 2: process by tile so nothing is loaded whole

exportLayer has no append mode — a second export into an existing table fails with relation already exists — so the first tile creates the table and later tiles are added through the destination layer's provider:

from qgis.core import QgsFeature

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

NX = NY = 4
dx, dy = (maxx - minx) / NX, (maxy - miny) / NY
destination, total = None, 0
for i in range(NX):
    for j in range(NY):
        x0, y0 = minx + i * dx, miny + j * dy
        # centroid in the tile, so a parcel on a tile edge is taken exactly once
        where = (f"geom && ST_MakeEnvelope({x0}, {y0}, {x0 + dx}, {y0 + dy}, 27700) AND "
                 f"ST_Centroid(geom) && ST_MakeEnvelope({x0}, {y0}, {x0 + dx}, {y0 + dy}, 27700)")
        tile = load_table("rutland_parcels", where=where, name=f"tile_{i}{j}", estimated=False)
        if tile.featureCount() == 0:
            continue
        result = processing.run("native:buffer", {"INPUT": tile, "DISTANCE": 5, "OUTPUT": "TEMPORARY_OUTPUT"})["OUTPUT"]
        if destination is None:
            write_layer(result, "parcels_buffered_5m")
            destination = load_table("parcels_buffered_5m", schema="results", key="gid")
        else:
            features = [QgsFeature(destination.fields()) for _ in range(result.featureCount())]
            for target, source in zip(features, result.getFeatures()):
                target.setGeometry(source.geometry())
                target["inspireid"] = source["inspireid"]
            ok, _ = destination.dataProvider().addFeatures(features)
            if not ok:
                raise RuntimeError(destination.dataProvider().lastError())
        total += tile.featureCount()
print(f"{total:,} parcels in 16 tiles;", execute("SELECT count(*) FROM results.parcels_buffered_5m"), "rows written")

21,253 parcels in 16 tiles; [[21253]] rows written

The && operator uses the GIST index, so each tile query is fast even on a very large table; the centroid test assigns a parcel that crosses a tile edge to exactly one tile.

Example 3: keep credentials out of a saved project

project = QgsProject.instance()
project.addMapLayer(load_table("rutland_parcels"))
for layer in project.mapLayers().values():
    if layer.providerType() == "postgres":
        source = QgsDataSourceUri(layer.source())
        assert not source.password(), f"{layer.name()} carries a password"
project.write("out/postgis_project.qgz")
print("password in the project:", project_contains("out/postgis_project.qgz", os.environ["PGPASSWORD"]))
project.clear()

password in the project: False

Checking for a password before writing, rather than stripping one, fails loudly when someone adds a layer with credentials in its URI.

Example 4: use PostGIS for the heavy lifting

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

rows = execute("""
    SELECT w.wd24nm,
           count(p.gid)                                    AS parcels,
           round(sum(ST_Area(p.geom))::numeric / 10000, 1) AS area_ha
    FROM public.rutland_wards w
    LEFT JOIN public.rutland_parcels p ON ST_Within(ST_PointOnSurface(p.geom), w.geom)
    GROUP BY w.wd24nm
    ORDER BY area_ha DESC
""")
for ward, parcels_n, area in rows[:5]:
    print(f"{ward:<28} {parcels_n:>6,} parcels  {area:>9} ha")

Braunston & Martinsthorpe     1,905 parcels     6038.0 ha
Exton                         1,106 parcels     5634.9 ha
Greetham                      1,128 parcels     4808.8 ha
Lyddington                    1,261 parcels     4036.8 ha
Normanton                     1,656 parcels     3929.4 ha

The spatial join runs on the server, using its indexes, and returns 15 rows. Doing the same in Python would mean transferring both tables across the network first. The counts match the GeoPandas version of the same join on the lineage page: 2,053 in Uppingham, 1,981 in Oakham South.

The page's QGIS session ends here:

QgsProject.instance().clear()
qgs.exitQgis()
print("QGIS closed")

QGIS closed

A live view, not a copy

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

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, a key column — and, if you let it, the credentials. Each omission has its own symptom: without a key column a view will not load at all, because the provider needs a stable identifier to fetch and edit rows. A table with no primary key still loads, with the provider falling back to PostgreSQL's ctid and logging that editing is disabled. 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. And a WHERE clause on the URI is evaluated by the server, so the rows never cross the network — always preferable to filtering after the fact.

Writing has several routes and they are not equivalent. QgsVectorLayerExporter is the general one and works with any URI; it creates or overwrites, it does not append, and it leaves the spatial index to you. native:importintopostgis is what the GUI uses, and it needs a named connection registered in the QGIS profile — with its username saved — which is why it fails in a fresh headless profile until you create one. ogr2ogr is excellent for bulk loads and can be driven with subprocess, as the setup here does through GDAL. And if you are already in pandas, GeoDataFrame.to_postgis() bypasses QGIS entirely.

Finally, credentials. Whatever you pass to setConnection() ends up in uri(), with either argument, and from there in project files, logs and run records. Leave user and password out of the URI and let libpq read them from the environment, a .pgpass file or a pg_service.conf entry, or keep them in QGIS's own authentication database and reference its configuration id.

Views, query layers and search paths

Views need an explicit key column: Pass a unique integer column as aKeyColumn, or the layer will not load (No key field for view/query given).
Query layers need surrounding parentheses: setDataSource("", "(SELECT …)", "geom", "", "gid"). Without them the provider treats the SQL as a table name.
Estimated metadata estimates counts too: a filtered layer's featureCount() became the planner's guess — 61,440 for 926 parcels. Turn it off, or count in SQL, when the number matters.
Declare the geometry type: An export of polygons that contain multipolygons into a Polygon column fails with Geometry type (MultiPolygon) does not match column type (Polygon) — see PostGIS write fails on SRID or geometry type.
search_path matters: An unqualified table name resolves against the role's search path. Always give the schema explicitly.
Column names: GDAL lowercases column names on the way into PostgreSQL; INSPIREID in the GeoPackage is inspireid in the table.
Long-running transactions block: A layer left in edit mode holds a transaction open. Commit or roll back promptly in scripts.
Saved connections persist: saveConnection writes to the QGIS profile; delete what a script created.

Internal links

FAQ

Why is my PostGIS layer invalid?

Read the QGIS message log (messageReceivedWithFormat on QGIS 4) — on QGIS 4.2 the provider's error() was empty for PostGIS failures while the log named the cause. The usual ones are wrong credentials, a table that needs its schema qualified, a view without a key column, or a query layer without parentheses.

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 — it creates or overwrites, returns a Qgis.VectorExportResult, and leaves the spatial index to you — or the native:importintopostgis algorithm if you have a named connection registered with its username. From pandas, GeoDataFrame.to_postgis() is simpler.

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

Do not put it in the URI at all: leave user and password empty in setConnection() and let libpq read them from PGUSER/PGPASSWORD, .pgpass or a service file. uri(False) does not remove a password that is there — its argument is about QGIS auth configurations. A project saved with such a layer contained the password; one saved from an environment-only URI did not.

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.