Append Features to a PostGIS Table in PyQGIS

Reading from a database is easy; writing to one that other people are reading at the same time is where the care goes. A nightly job that appends yesterday's survey points has to match the target schema exactly, has to not double-insert when it is run twice, and above all must not leave the table in a state where half the rows are today's and half are missing — which is precisely what happens when the process dies between a delete and an insert that were not in the same transaction.

This recipe belongs to PostGIS and Database Workflows in PyQGIS. It covers appending through the provider, wrapping multi-step writes in a transaction, mapping fields between mismatched schemas, and batching so memory stays flat on large inputs.

What a reader sees during a transactional refreshA vertical sequence has three participants: the script, the PostGIS server and a colleague reading the map. The script prepares features outside the transaction, then opens one, deletes yesterday's rows and inserts today's. Throughout that window the colleague's queries still return yesterday's complete data. Only after the commit do they see today's.Readers never see the gap between delete and insertyour scriptPostGISa colleague's mapBEGINDELETE survey_date = yesterdayINSERT 12 480 rowsCOMMITstill reads yesterday'scomplete datasetswitches to today's, atomicallyWithout the transaction there is a window in which the layer is empty for everyone

Prerequisites

  • QGIS 3.34 LTR or newer with a working PostGIS connection — see Connect to a PostGIS Database in PyQGIS.
  • A role with INSERT (and DELETE, if you refresh rather than accumulate) on the target table.
  • A target table that already exists, with a primary key and a geometry column whose SRID matches what you intend to write.

Append through the provider

The provider's addFeatures() is the direct route: no edit buffer, no undo stack, one round trip per batch.

from qgis.core import QgsFeature, QgsVectorLayer

target = QgsVectorLayer(uri.uri(False), "Readings", "postgres")
provider = target.dataProvider()

fields = target.fields()
batch = []

for source_feature in incoming.getFeatures():
    feature = QgsFeature(fields)
    feature.setGeometry(source_feature.geometry())
    feature["station_id"] = source_feature["station"]
    feature["reading_m"] = float(source_feature["value"])
    feature["survey_date"] = source_feature["captured"]
    batch.append(feature)

ok, written = provider.addFeatures(batch)
if not ok:
    raise RuntimeError(provider.error().message())
print(f"inserted {len(written)} rows")

Breakdown: Constructing QgsFeature(fields) from the target layer's fields — not the source's — is what keeps the attribute order correct; assigning by name then works regardless of how the source is ordered. Do not set the primary key: leave it unset and let the database's sequence assign it, otherwise two runs collide. addFeatures() returns a success flag and the list of features as written, with their new identifiers filled in, which is useful when a later step needs those ids. A failure returns False rather than raising, so the check is not optional.

Wrap the refresh in a transaction

addFeatures() on its own is atomic for that one call. A delete-then-insert refresh is two calls, and needs an explicit transaction to be atomic as a pair.

from qgis.core import QgsTransaction

transaction = QgsTransaction.create([target])
if not transaction.begin():
    raise RuntimeError("could not begin transaction")

try:
    transaction.executeSql(
        "DELETE FROM public.readings WHERE survey_date = current_date - 1",
        True,
    )
    ok, _ = provider.addFeatures(batch)
    if not ok:
        raise RuntimeError(provider.error().message())
    if not transaction.commit():
        raise RuntimeError("commit failed")
except Exception:
    transaction.rollback()
    raise

Breakdown: QgsTransaction.create() takes the layers that should share one database transaction — passing several keeps a multi-table write consistent. The second argument to executeSql() marks the statement as a modification so it participates in the transaction's dirty state. The try/except that rolls back and re-raises is the whole point: without it a failed insert leaves the delete committed and the table short of a day's data. Re-raising rather than swallowing means a scheduled runner sees a non-zero exit, which is what triggers the alert described in handling errors in unattended scripts.

Commit in batches on large inputs

Building a list of two million QgsFeature objects will exhaust memory long before the insert starts. Flush periodically instead.

BATCH = 5000
batch = []
total = 0

for source_feature in incoming.getFeatures():
    batch.append(build_feature(source_feature, fields))
    if len(batch) >= BATCH:
        ok, written = provider.addFeatures(batch)
        if not ok:
            raise RuntimeError(provider.error().message())
        total += len(written)
        batch.clear()

if batch:
    ok, written = provider.addFeatures(batch)
    total += len(written)
print("appended", total)

Breakdown: Five thousand features per call is a good default: large enough that per-statement overhead disappears, small enough that memory stays flat and a failure is easy to locate. batch.clear() rather than rebinding to a new list keeps the same object and avoids surprising a reference held elsewhere. Note the trailing flush — the loop leaves a partial batch behind whenever the count is not an exact multiple, and forgetting it silently drops up to 4 999 rows.

Memory during an append: accumulate versus flushTwo curves over the same run. Accumulating every feature before a single insert climbs steadily to a peak that risks exhausting memory. Flushing every five thousand features produces a low sawtooth that never rises above a small constant.Flushing turns a climbing curve into a flat oneRAMfeatures processedaccumulate everythingflush every 5 000The sawtooth is the batch filling and being released after each insert

Make the job safe to run twice

Scheduled jobs get re-run: by a retry, by an operator, by a colleague who did not know it had already gone. Give the table a natural key and let the database refuse the duplicate.

connection.executeSql("""
    ALTER TABLE public.readings
    ADD CONSTRAINT readings_station_date_unique UNIQUE (station_id, survey_date)
""")

Breakdown: With the constraint in place, a second run fails loudly on the first duplicate instead of quietly doubling every count in every downstream map. Combine it with the delete-then-insert pattern above and re-running becomes genuinely idempotent: yesterday's rows go, today's arrive, and the totals are right whether the job ran once or five times.

Reconcile after the write

An append that reports success has still only told you that the statements executed. Whether the table now holds what it should is a separate question, and asking it costs one query.

expected = incoming.featureCount()

before = connection.executeSql(
    "SELECT count(*) FROM public.readings WHERE survey_date = current_date - 1"
)[0][0]

# … the transactional refresh …

after = connection.executeSql(
    "SELECT count(*) FROM public.readings WHERE survey_date = current_date - 1"
)[0][0]

log.info("source=%d before=%d after=%d", expected, before, after)
if after != expected:
    raise RuntimeError(f"expected {expected} rows for yesterday, found {after}")

Breakdown: Counting the same slice before and after gives three numbers that together describe what happened: how many rows arrived, how many were already there, and how many remain. A mismatch between expected and after catches the failures that no exception reports — a source feed that truncated, a filter that excluded more than intended, a partial batch that was never flushed. Raising on the mismatch converts a wrong result into a failed job, which is the behaviour a scheduler can act on, following the plausibility discipline in Handle Errors and Logging in Unattended Scripts.

Two further checks are worth running periodically rather than nightly: SELECT count(*) FROM readings WHERE geom IS NULL finds rows that arrived without geometry, and SELECT ST_SRID(geom), count(*) FROM readings GROUP BY 1 proves that every row really carries the SRID the column claims. Both are cheap enough to run weekly and both find problems that no single night's log would reveal.

Three counts that describe what the job didThe source feature count, the row count for that day before the refresh and the row count after it are logged together. When the source and after counts match, the run is verified. When they diverge, the job raises rather than reporting success, catching a truncated feed that no exception would have reported.Success is a number that matches, not a statement that ransource features12 480rows before12 480 from the last runrows after12 480 — verifiedsource 41 · after 41 — the feed truncated overnightcounts agree with each other and with nothing useful — compare against before

QGIS version compatibility

The examples target QGIS 3.34 LTR (Python 3.12).

QGIS versionPythonNotes
3.22 LTR3.9addFeatures() and QgsTransaction behave as shown.
3.28 LTR3.9Adds provider feature flags for fast insert; no code change needed.
3.34 LTR3.12Baseline for this page.
3.40 / 3.443.12Transaction groups can be enabled per project, which changes when edits are flushed in the GUI but not in this script.

Troubleshooting

  • "duplicate key value violates unique constraint". The primary key was set on the incoming features. Leave it unset so the sequence assigns it — or, if this is the natural-key constraint firing, the job has already run.
  • Geometry type mismatch. The target column is declared as MultiPolygon and the source features are single polygons. Convert with geometry.convertToMultiType() before appending.
  • SRID mismatch. PostGIS rejects geometry whose SRID differs from the column constraint. Reproject before writing — the transformation itself is covered in Transform Point Coordinates in PyQGIS.
  • Nothing was inserted and no error appeared. addFeatures() returned False and the result was not checked. Always test it.
  • The insert is extremely slow. Every row is being committed separately, or a trigger fires per row. Batch the calls, and consider dropping non-essential indexes for a very large bulk load and rebuilding them afterwards.
  • Attribute values are all null. The features were built from the source layer's fields rather than the target's, so assignment by name wrote into positions the target does not have.

Conclusion

Appending to PostGIS is provider.addFeatures() with features built from the target's fields, the primary key left alone, and the result checked. Anything that deletes as well as inserts belongs inside a QgsTransaction with a rollback on failure; anything large belongs in batches with a trailing flush; and anything scheduled deserves a unique constraint so a second run fails loudly rather than doubling the data.

Frequently Asked Questions

Should I use the layer's edit buffer instead?startEditing() / commitChanges() builds an undo stack in memory and is the right choice inside an interactive plugin. For a bulk load, going straight to the provider avoids that overhead entirely.

How do I get the identifiers the database assigned?addFeatures() returns the written features with their new ids populated, provided the provider can read them back — which it can for a PostGIS table with a serial primary key.

Can I append to a query layer? No. Query layers are read-only. Append to the underlying table and let the query pick the rows up.

What happens if the source has fields the target lacks? They are ignored when you build features from the target's field list. Adding the column to the target first is the deliberate alternative — see Add a Field to a Layer in PyQGIS.

Does a transaction lock the table for readers? No. PostgreSQL readers are never blocked by a writer; they continue to see the pre-commit snapshot. Two concurrent writers touching the same rows will block each other, which is the behaviour you want.