Load a PostGIS Query Layer in PyQGIS

A query layer is the shortest path between a question and a map. Instead of running three Processing algorithms to join, aggregate and filter, you write the SQL you would have written anyway and hand it to QGIS as a layer — stylable, labelable, exportable, and recalculated by the database every time it is drawn. It is the single biggest lever PostGIS gives a PyQGIS script, and the one most often left unused.

This recipe belongs to PostGIS and Database Workflows in PyQGIS. It covers the exact URI shape that turns SQL into a layer, the two declarations that stop QGIS running an expensive probe query, and when a materialised view is the better answer.

Three algorithms and two temporary files, or one queryThe upper path runs a spatial join, then a statistics-by-category step, then a filter, writing two intermediate layers along the way and moving every feature into QGIS memory. The lower path expresses the same question as one SQL statement evaluated by PostGIS, which returns only the finished rows.Same answer, one round trip instead of threealgorithm chainspatial joinstatisticsfilter2 temp layersquery layerSELECT … JOIN … GROUP BY … evaluated by PostGIS0 temp layers

Prerequisites

  • QGIS 3.34 LTR or newer with a working PostGIS connection — see Connect to a PostGIS Database in PyQGIS.
  • Read access to the tables involved, and enough SQL to write the statement you want.
  • A column in the result that is unique across every row — without it the layer will misbehave in ways that look like data corruption.

The subquery form

The difference between a table layer and a query layer is one argument: pass an empty schema and put a parenthesised SELECT where the table name goes.

from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("db.example.org", "5432", "cityworks", "gis_reader", "")
uri.setAuthConfigId("a1b2c3d")

sql = """(
    SELECT w.gid,
           w.geom,
           w.ward_name,
           count(i.id)                       AS incidents,
           count(i.id) / (ST_Area(w.geom) / 1000000.0) AS per_km2
    FROM public.wards w
    LEFT JOIN public.incidents i ON ST_Contains(w.geom, i.geom)
    GROUP BY w.gid, w.geom, w.ward_name
)"""

uri.setDataSource("", sql, "geom", "", "gid")
layer = QgsVectorLayer(uri.uri(False), "Incidents per ward", "postgres")
print(layer.isValid(), layer.featureCount())

Breakdown: The outer parentheses are required — they are how the provider recognises a subquery rather than a table name. gid is nominated as the key column and appears in the GROUP BY, which guarantees one output row per ward and keeps the identifier unique. The density calculation divides by ST_Area in square metres, so the layer arrives ready to style with a graduated renderer with no further processing. Everything inside the parentheses runs on the server against its spatial index; QGIS receives finished rows.

Declare the geometry type and SRID

By default the provider does not know what geometry a subquery will produce or in which CRS, so it runs an extra query to find out — on a large table that probe can take longer than the query itself, and it runs before anything appears on screen.

uri.setSrid("27700")
uri.setWkbType(QgsWkbTypes.MultiPolygon)
uri.setUseEstimatedMetadata(True)

Breakdown: setSrid() and setWkbType() tell the provider what to expect, so it skips the type-detection query entirely. setUseEstimatedMetadata(True) is the bigger win on large tables: instead of computing the exact extent with a full scan, the provider takes PostgreSQL's own statistics estimate. The extent is then approximate — fine for zooming, wrong for anything that measures — so leave it off when the extent feeds a calculation, and turn it on whenever a human is waiting for the canvas.

Where the seconds go when a query layer loadsThe upper timeline shows an undeclared query layer: a short connection step, a long geometry type detection step, a long exact extent scan, and finally the query itself. The lower timeline shows the same layer with SRID, geometry type and estimated metadata declared, where only the connection and the query remain.Two declarations remove most of the waitundeclaredconnectdetect geometry typeexact extent scan over every rowthe querydeclaredconnectthe querysetSrid + setWkbTypeestimated metadata on0 sfirst paintEstimated extents are approximate — turn them off when the extent feeds a measurement

Parameterising the statement safely

Query layers are frequently built from user input — a date range, a selected category — and string formatting is the wrong tool for that job.

from qgis.core import QgsProviderRegistry

connection = QgsProviderRegistry.instance().providerMetadata("postgres").createConnection("cityworks")

category = "flooding"
quoted = connection.quotedValue(category)          # returns 'flooding' with correct escaping
column = connection.quotedIdentifier("incident_type")

sql = f"""(
    SELECT id, geom, reported_at
    FROM public.incidents
    WHERE {column} = {quoted}
)"""

Breakdown: quotedValue() and quotedIdentifier() apply the database's own escaping rules — the same functions QGIS uses internally — so a value containing an apostrophe becomes a valid literal rather than a syntax error or an injection. Building the string with f-strings around those helpers is safe; building it with f-strings around raw user input is not. The provider does not support bind parameters in a layer URI, which is precisely why these helpers exist.

When to use a view instead

A query layer re-runs its statement on every canvas refresh. That is exactly what you want for a cheap query over live data, and exactly what you do not want for a five-second aggregate that four people have open.

Move the statement into the database when it is expensive or shared:

connection.executeSql("""
    CREATE MATERIALIZED VIEW public.incidents_per_ward AS
    SELECT w.gid, w.geom, w.ward_name, count(i.id) AS incidents
    FROM public.wards w
    LEFT JOIN public.incidents i ON ST_Contains(w.geom, i.geom)
    GROUP BY w.gid, w.geom, w.ward_name
""")
connection.executeSql("CREATE UNIQUE INDEX ON public.incidents_per_ward (gid)")
connection.executeSql("CREATE INDEX ON public.incidents_per_ward USING GIST (geom)")

Breakdown: A materialised view stores the result, so the layer becomes an ordinary table layer with proper statistics and indexes. The unique index on gid gives QGIS its key column and lets PostgreSQL refresh the view concurrently; the GiST index on the geometry makes canvas panning fast. The cost is staleness — a scheduled REFRESH MATERIALIZED VIEW CONCURRENTLY decides how fresh the map is, which fits naturally into the scheduled job patterns.

Keep the SQL somewhere you can review it

A twenty-line statement embedded in a Python string is hard to read, impossible to run in psql without editing, and invisible to anyone reviewing the change. Keep the SQL in its own file and load it.

from pathlib import Path

SQL_DIR = Path(__file__).parent / "sql"

def query_layer(uri, name, sql_file, **params):
    statement = (SQL_DIR / sql_file).read_text().strip().rstrip(";")
    uri.setDataSource("", f"({statement.format(**params)})", "geom", "", "gid")
    layer = QgsVectorLayer(uri.uri(False), name, "postgres")
    if not layer.isValid():
        raise RuntimeError(f"{name}: {layer.dataProvider().error().message()}")
    return layer

wards = query_layer(uri, "Incidents per ward", "incidents_per_ward.sql", days=30)

Breakdown: Reading the file at call time means the statement can be opened in a database client, explained, and profiled as ordinary SQL — the thing you actually want when a query layer is slow. Stripping a trailing semicolon matters because the provider wraps the statement in its own parentheses and a semicolon inside them is a syntax error. format(**params) handles simple substitutions such as a day count; anything derived from user input still has to go through the connection's quotedValue() helper rather than straight into the template.

The same files then serve three purposes: they are what the query layer runs, what a materialised view is created from, and what a reviewer reads in a pull request. Placing them beside the plugin or the scripts, under version control, is the cheapest way to stop a project's real logic living in string literals — the same instinct behind keeping styles as files in Save and Load a QML Style in PyQGIS.

One SQL file, three consumersA statement kept in its own file is loaded by the query layer, used to create a materialised view, and read directly during code review. The same statement embedded in a Python string literal can only be used by the code that contains it, and cannot be run in a database client without editing.A statement in a file can be explained; one in a string cannotincidents_per_ward.sqlunder version controla query layer in QGISa materialised viewEXPLAIN ANALYZE in psqlsql = """…"""reachable only from hereInterpolate values through quotedValue(), never straight into the template

QGIS version compatibility

The examples target QGIS 3.34 LTR (Python 3.12).

QGIS versionPythonNotes
3.22 LTR3.9Subquery layers work identically; quotedValue() on the connection object is unavailable, so escape with QgsExpression.quotedValue().
3.28 LTR3.9Connection helpers available; behaviour matches this page.
3.34 LTR3.12Baseline for this page.
3.40 / 3.443.12The SQL query dialog gained saved queries; the Python API is unchanged.

Troubleshooting

  • Every feature has the same identifier, or features vanish when you zoom. The key column is not unique across the result. A join that multiplies rows is the usual cause; add the joined table's key to the SELECT and nominate a composite that is genuinely unique, or aggregate the duplicates away.
  • The layer is invalid and the message mentions the geometry column. The subquery does not expose a column with that name, or the name is aliased. Alias the geometry explicitly as geom and pass geom.
  • Loading takes many seconds before anything draws. The extent probe is running. Declare the SRID and geometry type, and enable estimated metadata.
  • Attributes are all strings. PostgreSQL could not infer types through the subquery — cast them explicitly in the SELECT, for example count(i.id)::int.
  • A syntax error appears only when a filter is applied. QGIS wraps the subquery in its own WHERE, so a statement ending in LIMIT or ORDER BY can become invalid. Wrap yours in an extra SELECT * FROM ( … ) AS q.
  • The query is correct but returns nothing through QGIS. Check the role: a query layer runs as the connecting user, and row-level security policies apply to it.

Conclusion

A query layer is an ordinary layer whose table happens to be a parenthesised SELECT. Give it a genuinely unique key column, declare the SRID and geometry type so the provider skips its probe, escape any interpolated values with the connection's quoting helpers, and promote the statement to a materialised view once it becomes expensive or widely used.

Frequently Asked Questions

Can a query layer be edited? No. Anything built from a subquery is read-only, because the provider cannot know how to map an edit back to the underlying rows. Edit the source tables and let the query reflect the change.

Does the query run once or on every redraw? On every redraw, filtered by the current canvas extent. That is why declaring the geometry type matters and why an expensive statement belongs in a materialised view.

Can I use a CTE or a window function? Yes — anything valid inside a subquery works, including WITH clauses and window functions. Wrap the whole thing so the outer statement is a single SELECT.

How do I combine a query layer with a Processing algorithm? Pass the layer object straight into processing.run(). Algorithms accept it exactly as they accept a file-based layer, and the provider streams the result rows in — see Run a Processing Algorithm from a Script.

Is a query layer slower than a table layer? For a simple filter, no — the planner handles it much as it would a view. For a multi-table aggregate, it is exactly as slow as the query, every time the canvas refreshes, which is the argument for materialising it.