Qgis Impl Postgis

qgis-impl-postgis

Quick Reference

Core Classes

QgsDataSourceUri Key Methods

Provider Names

---

Critical Warnings

NEVER store passwords in plain-text URIs in production code. ALWAYS use QgsAuthManager with stored authentication configurations (authcfg). Plain-text passwords leak into logs, project files, and connection strings.

NEVER load PostGIS views without specifying a unique key column -- this causes undefined behavior, duplicate features, and severe performance degradation. ALWAYS pass the key column as the 5th argument to setDataSource().

ALWAYS use uri.uri(False) when passing URIs to QgsVectorLayer or QgsRasterLayer constructors. Passing True (or omitting the argument) expands the authcfg reference and exposes credentials.

ALWAYS use QgsDataSourceUri to construct connection strings. NEVER manually concatenate URI strings -- this causes escaping errors with special characters in passwords, table names, or schema names.

ALWAYS check layer.isValid() immediately after constructing a PostGIS layer. Invalid layers fail silently and produce empty results.

NEVER use estimatedmetadata=true for layers where row count accuracy matters (e.g., feature counting for reports). Estimated metadata skips expensive table scans but returns approximate counts.

---

Decision Tree

Need to work with PostGIS?
├── Loading a layer?
│   ├── From a table → setDataSource(schema, table, geomCol)
│   ├── From a view → setDataSource(schema, view, geomCol, "", keyCol)  # key required
│   ├── From SQL query → setDataSource("", "(SELECT ...)", geomCol, "", keyCol)
│   └── Raster data → Use "postgresraster" provider with encodeUri()
├── Executing SQL?
│   ├── Non-spatial query → conn.executeSql("SELECT ...")
│   └── Spatial result needed → Load as SQL query layer (above)
├── Discovering schema?
│   ├── List schemas → conn.schemas()
│   └── List tables → conn.tables(schemaName)
├── Exporting to PostGIS?
│   ├── Processing toolbox → processing.run("native:importintopostgis", {...})
│   └── Direct export → QgsVectorFileWriter.writeAsVectorFormatV3()
└── Authentication?
    ├── Development/testing → setConnection() with user/password (temporary only)
    └── Production → setAuthConfigId() with QgsAuthManager config

---

Essential Patterns

Pattern 1: Connect and Load a Table

from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")  # stored auth config
uri.setDataSource("public", "roads", "geom")

layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")
assert layer.isValid(), f"Layer failed to load: {layer.error().message()}"

QgsProject.instance().addMapLayer(layer)

Pattern 2: Load a View (Key Column Required)

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")
# 5th argument = unique key column -- REQUIRED for views
uri.setDataSource("public", "roads_summary_view", "geom", "", "view_id")

layer = QgsVectorLayer(uri.uri(False), "Roads Summary", "postgres")
assert layer.isValid()

Pattern 3: Load a SQL Query as Layer

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")

sql = "(SELECT r.gid, r.name, r.geom FROM roads r WHERE r.type = 'highway')"
uri.setDataSource("", sql, "geom", "", "gid")

layer = QgsVectorLayer(uri.uri(False), "Highways", "postgres")
assert layer.isValid()

Pattern 4: Load with SQL Filter and Performance Options

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")

# SQL filter as 4th argument to setDataSource
uri.setDataSource("public", "parcels", "geom", "area_sqm > 1000", "gid")
uri.setUseEstimatedMetadata(True)  # faster for large tables
uri.setParam("checkPrimaryKeyUnicity", "0")  # skip uniqueness check

layer = QgsVectorLayer(uri.uri(False), "Large Parcels", "postgres")
assert layer.isValid()

Pattern 5: Execute SQL Queries

from qgis.core import QgsProviderRegistry, QgsDataSourceUri

# Option A: Using a stored connection name
md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("My PostGIS Server")  # stored connection name

results = conn.executeSql("SELECT count(*) FROM public.roads")
print(f"Row count: {results[0][0]}")

# Option B: Using a URI
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")

conn = md.createConnection(uri.uri(False), {})
results = conn.executeSql("SELECT DISTINCT road_type FROM public.roads ORDER BY road_type")

Pattern 6: Schema and Table Discovery

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("My PostGIS Server")

# List all schemas
schemas = conn.schemas()
for schema in schemas:
    print(f"Schema: {schema}")

# List tables in a schema
tables = conn.tables("public")
for table in tables:
    print(f"Table: {table.tableName()}, "
          f"Geometry: {table.geometryColumn()}, "
          f"Type: {table.geometryColumnTypes()}")

Pattern 7: Export Layer to PostGIS

import processing

# Using the Processing algorithm (recommended)
result = processing.run("native:importintopostgis", {
    'INPUT': source_layer,
    'DATABASE': 'My PostGIS Server',  # stored connection name
    'SCHEMA': 'public',
    'TABLENAME': 'exported_roads',
    'PRIMARY_KEY': 'id',
    'GEOMETRY_COLUMN': 'geom',
    'ENCODING': 'UTF-8',
    'OVERWRITE': True,
    'CREATEINDEX': True,
    'LOWERCASE_NAMES': True,
    'DROP_STRING_LENGTH': False,
    'FORCE_SINGLEPART': False
})

Pattern 8: Export with QgsVectorFileWriter

from qgis.core import QgsVectorFileWriter, QgsCoordinateTransformContext

save_options = QgsVectorFileWriter.SaveVectorOptions()
save_options.driverName = "PostgreSQL"
save_options.layerName = "new_table"

# ALWAYS use authcfg in production instead of plain password
uri_str = "PG:host=localhost port=5432 dbname=gisdb authcfg=my_postgis_auth"
error = QgsVectorFileWriter.writeAsVectorFormatV3(
    layer,
    uri_str,
    QgsCoordinateTransformContext(),
    save_options
)
if error[0] != QgsVectorFileWriter.NoError:
    raise RuntimeError(f"Export failed: {error[1]}")

---

PostGIS Raster Loading

from qgis.core import QgsProviderRegistry, QgsDataSourceUri, QgsRasterLayer

uri_config = {
    'dbname': 'gisdb',
    'host': 'localhost',
    'port': '5432',
    'sslmode': QgsDataSourceUri.SslDisable,
    'authcfg': 'my_postgis_auth',
    'schema': 'public',
    'table': 'elevation_tiles',
    'geometrycolumn': 'rast',
    'mode': '2'  # 0=one tile per row, 1=one layer per row, 2=union all tiles
}

md = QgsProviderRegistry.instance().providerMetadata('postgresraster')
uri = QgsDataSourceUri(md.encodeUri(uri_config))

rlayer = QgsRasterLayer(uri.uri(False), "Elevation", "postgresraster")
assert rlayer.isValid(), f"Raster layer failed: {rlayer.error().message()}"

QgsProject.instance().addMapLayer(rlayer)

Raster Mode Values

---

Authentication

Using QgsAuthManager (Production)

from qgis.core import QgsApplication, QgsAuthMethodConfig

# Create a new auth config
auth_mgr = QgsApplication.authManager()
config = QgsAuthMethodConfig()
config.setName("My PostGIS Server")
config.setMethod("Basic")
config.setConfig("username", "db_user")
config.setConfig("password", "db_password")

# Store it -- returns (success, config) with config.id() populated
success, config = auth_mgr.storeAuthenticationConfig(config)
assert success, "Failed to store auth config"
auth_config_id = config.id()  # e.g., "abc123"

# Use in URI
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId(auth_config_id)
uri.setDataSource("public", "roads", "geom")

layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")

Retrieving an Existing Auth Config

auth_mgr = QgsApplication.authManager()
config = QgsAuthMethodConfig()
auth_mgr.loadAuthenticationConfig("abc123", config, True)
# config now contains stored credentials

---

Connection Management

Stored Connections vs URI

Using Stored Connections

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")

# List all stored connections
connections = md.connections()
for name, conn in connections.items():
    print(f"Connection: {name}")

# Use a stored connection
conn = md.createConnection("My PostGIS Server")
tables = conn.tables("public")

---

Common Operations

Check if PostGIS Extension is Installed

conn = md.createConnection("My PostGIS Server")
result = conn.executeSql("SELECT PostGIS_Version()")
print(f"PostGIS version: {result[0][0]}")

Create a Spatial Index

conn.executeSql("CREATE INDEX IF NOT EXISTS idx_roads_geom ON public.roads USING GIST (geom)")

Vacuum and Analyze

conn.executeSql("VACUUM ANALYZE public.roads")

Count Features with Filter

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")
uri.setDataSource("public", "roads", "geom", "road_type = 'highway'")

layer = QgsVectorLayer(uri.uri(False), "Highways", "postgres")
assert layer.isValid()
print(f"Feature count: {layer.featureCount()}")

---

Reference Links

• references/methods.md -- Complete API signatures for QgsDataSourceUri and database connection classes
• references/examples.md -- Working code examples for all PostGIS operations
• references/anti-patterns.md -- What NOT to do with PostGIS connections

Official Sources

• https://docs.qgis.org/3.34/en/docs/pyqgis_developer_cookbook/loadlayer.html
• https://qgis.org/pyqgis/3.34/core/QgsDataSourceUri.html
• https://qgis.org/pyqgis/3.34/core/QgsAbstractDatabaseProviderConnection.html
• https://qgis.org/pyqgis/3.34/core/QgsAuthManager.html
• https://qgis.org/pyqgis/3.34/core/QgsProviderRegistry.html

Anti-Patterns (PostGIS / PyQGIS)

1. Plain-Text Passwords in URIs

# WRONG: Credentials stored in plain text -- leaks to logs, project files, history
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "mydb", "admin", "s3cret_pw!")
uri.setDataSource("public", "roads", "geom")
layer = QgsVectorLayer(uri.uri(), "Roads", "postgres")  # uri() expands credentials

# CORRECT: Use QgsAuthManager with stored auth config
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "mydb", "", "")
uri.setAuthConfigId("my_auth_config")
uri.setDataSource("public", "roads", "geom")
layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")

WHY: Plain-text passwords appear in QGIS project files (.qgz/.qgs), server logs, layer metadata, and uri.uri() output. QgsAuthManager stores credentials in an encrypted database (qgis-auth.db) and uri(False) prevents credential expansion.

---

2. Calling uri() Without False

# WRONG: uri() without argument defaults to True, expanding authcfg to plain credentials
layer = QgsVectorLayer(uri.uri(), "Roads", "postgres")

# ALSO WRONG: Explicitly passing True
layer = QgsVectorLayer(uri.uri(True), "Roads", "postgres")

# CORRECT: ALWAYS pass False to prevent credential expansion
layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")

WHY: uri(True) resolves the authcfg reference and embeds the actual username/password into the URI string. This string then appears in layer metadata, debug output, and logs.

---

3. Loading Views Without a Key Column

# WRONG: No key column specified for a view
uri.setDataSource("public", "my_view", "geom")
layer = QgsVectorLayer(uri.uri(False), "My View", "postgres")
# Results: duplicate features, missing features, crashes on edit, wrong feature count

# CORRECT: ALWAYS specify a unique key column for views
uri.setDataSource("public", "my_view", "geom", "", "view_id")
layer = QgsVectorLayer(uri.uri(False), "My View", "postgres")

WHY: QGIS uses the primary key to uniquely identify features. Tables have primary keys in their schema, but views do not. Without an explicit key column, QGIS uses ctid (a physical row identifier) which is unstable for views and causes duplicate or missing features.

---

4. Manual URI String Concatenation

# WRONG: Manual string building -- vulnerable to injection and escaping errors
uri_str = f"dbname='my db' host=localhost port=5432 user='admin' password='p@ss\"word' table=\"public\".\"roads\" (geom)"
layer = QgsVectorLayer(uri_str, "Roads", "postgres")

# CORRECT: Use QgsDataSourceUri which handles escaping
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "my db", "", "")
uri.setAuthConfigId("my_auth")
uri.setDataSource("public", "roads", "geom")
layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")

WHY: QgsDataSourceUri properly escapes special characters in database names, schema names, table names, and passwords. Manual concatenation breaks with spaces, quotes, backslashes, and other special characters.

---

5. Not Checking Layer Validity

# WRONG: Using a layer without checking validity
uri.setDataSource("public", "nonexistent_table", "geom")
layer = QgsVectorLayer(uri.uri(False), "Data", "postgres")
QgsProject.instance().addMapLayer(layer)  # adds an invalid layer silently
for feature in layer.getFeatures():       # iterates over nothing, no error
    process(feature)

# CORRECT: ALWAYS check validity immediately after construction
layer = QgsVectorLayer(uri.uri(False), "Data", "postgres")
if not layer.isValid():
    error_msg = layer.error().message()
    raise RuntimeError(f"Failed to load PostGIS layer: {error_msg}")

WHY: PostGIS layer construction never raises exceptions. A wrong host, missing table, bad credentials, or network timeout all produce an invalid layer that silently returns zero features. Code continues executing with empty data.

---

6. Using estimatedmetadata for Accurate Counts

# WRONG: Using estimated metadata when exact counts are required
uri.setUseEstimatedMetadata(True)
layer = QgsVectorLayer(uri.uri(False), "Data", "postgres")
total = layer.featureCount()  # returns approximate count from pg_class.reltuples
report.set_total_features(total)  # report shows wrong number

# CORRECT: Disable estimated metadata when accuracy matters
uri.setUseEstimatedMetadata(False)  # default
layer = QgsVectorLayer(uri.uri(False), "Data", "postgres")
total = layer.featureCount()  # runs SELECT COUNT(*) -- accurate but slower

WHY: With estimatedmetadata=true, QGIS reads pg_class.reltuples instead of running SELECT COUNT(*). After bulk inserts, deletes, or without recent ANALYZE, this value is stale. Use estimated metadata for UI responsiveness on large tables, but NEVER for reporting or logic that depends on exact counts.

---

7. SQL Subquery Without Parentheses

# WRONG: SQL query not wrapped in parentheses
uri.setDataSource("", "SELECT * FROM roads WHERE type='highway'", "geom", "", "gid")

# CORRECT: Wrap SQL in parentheses
uri.setDataSource("", "(SELECT * FROM roads WHERE type='highway')", "geom", "", "gid")

WHY: The PostGIS provider interprets the table argument as a table name unless wrapped in parentheses. Without them, the SQL is treated as a literal table name, causing a "relation does not exist" error.

---

8. Missing Key Column in SQL Subqueries

# WRONG: SQL subquery without a key column
uri.setDataSource("", "(SELECT name, geom FROM roads)", "geom")
layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")
# Results: unpredictable feature IDs, editing fails, possible duplicates

# CORRECT: Include a unique key column in SELECT and specify it
uri.setDataSource("", "(SELECT gid, name, geom FROM roads)", "geom", "", "gid")
layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")

WHY: Without a key column, QGIS auto-generates feature IDs which are unstable across sessions. Editing, selection, and joins fail or produce incorrect results.

---

9. Forgetting to Create Spatial Index After Import

# WRONG: Import data and start querying without an index
processing.run("native:importintopostgis", {
    'INPUT': layer,
    'DATABASE': 'My DB',
    'SCHEMA': 'public',
    'TABLENAME': 'big_dataset',
    'CREATEINDEX': False,  # no spatial index
    'OVERWRITE': True
})
# Spatial queries on big_dataset are now extremely slow

# CORRECT: ALWAYS create a spatial index for spatial tables
processing.run("native:importintopostgis", {
    'INPUT': layer,
    'DATABASE': 'My DB',
    'SCHEMA': 'public',
    'TABLENAME': 'big_dataset',
    'CREATEINDEX': True,   # creates GIST index on geometry column
    'OVERWRITE': True
})

WHY: Without a spatial index (GiST), every spatial query (intersection, bounding box, nearest neighbor) performs a full table scan. On tables with more than a few thousand rows, this causes severe performance degradation.

---

10. Using Wrong Provider String

# WRONG: Using "postgis" as provider name
layer = QgsVectorLayer(uri.uri(False), "Data", "postgis")  # no such provider

# WRONG: Using "postgresql" as provider name
layer = QgsVectorLayer(uri.uri(False), "Data", "postgresql")  # no such provider

# CORRECT: Provider name is "postgres" for vector
layer = QgsVectorLayer(uri.uri(False), "Data", "postgres")

# CORRECT: Provider name is "postgresraster" for raster
rlayer = QgsRasterLayer(uri.uri(False), "Raster", "postgresraster")

WHY: QGIS registers providers by exact string. The vector provider is "postgres" (not "postgis" or "postgresql"). The raster provider is "postgresraster". Using the wrong string creates an invalid layer with no error message.

---

11. Connecting Without SSL in Production

# WRONG: Disabling SSL for production database over network
uri.setConnection("remote-db.example.com", "5432", "prod_db", "", "",
                  QgsDataSourceUri.SslDisable)

# CORRECT: Use SSL for remote connections
uri.setConnection("remote-db.example.com", "5432", "prod_db", "", "",
                  QgsDataSourceUri.SslRequire)  # or SslVerifyFull for maximum security

WHY: Without SSL, database credentials and query data travel in plain text over the network. For localhost connections, SslDisable is acceptable. For any remote connection, ALWAYS use SslRequire or SslVerifyFull.

Working Code Examples (PostGIS / PyQGIS)

Example 1: Basic Table Connection with Auth Config

from qgis.core import QgsDataSourceUri, QgsVectorLayer, QgsProject

uri = QgsDataSourceUri()
uri.setConnection("db.example.com", "5432", "city_gis", "", "")
uri.setAuthConfigId("pg_prod_auth")
uri.setDataSource("public", "buildings", "geom")

layer = QgsVectorLayer(uri.uri(False), "Buildings", "postgres")
assert layer.isValid(), f"Failed to load: {layer.error().message()}"

QgsProject.instance().addMapLayer(layer)
print(f"Loaded {layer.featureCount()} buildings")

---

Example 2: Load a Database View

from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "analytics_db", "", "")
uri.setAuthConfigId("pg_local_auth")

# Views REQUIRE a unique key column -- without it, behavior is undefined
uri.setDataSource("reporting", "monthly_sales_view", "location", "", "report_id")

layer = QgsVectorLayer(uri.uri(False), "Monthly Sales", "postgres")
assert layer.isValid()

---

Example 3: SQL Subquery as Layer

from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "transport_db", "", "")
uri.setAuthConfigId("pg_transport")

# Wrap SQL in parentheses, use empty string for schema
sql_query = """(
    SELECT r.gid, r.road_name, r.speed_limit, r.geom
    FROM roads r
    JOIN road_conditions rc ON r.gid = rc.road_id
    WHERE rc.condition = 'poor'
      AND r.speed_limit > 80
)"""

uri.setDataSource("", sql_query, "geom", "", "gid")

layer = QgsVectorLayer(uri.uri(False), "Poor Condition Highways", "postgres")
assert layer.isValid()
print(f"Found {layer.featureCount()} roads needing repair")

---

Example 4: Schema Discovery and Table Listing

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("Production PostGIS")

# List all schemas
for schema in conn.schemas():
    print(f"\nSchema: {schema}")

    # List tables with geometry in each schema
    for table in conn.tables(schema):
        geom_col = table.geometryColumn()
        if geom_col:
            pk_cols = ", ".join(table.primaryKeyColumns())
            print(f"  {table.tableName()} "
                  f"(geom: {geom_col}, pk: {pk_cols})")

---

Example 5: Execute SQL and Process Results

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("My PostGIS Server")

# Simple aggregation query
results = conn.executeSql("""
    SELECT road_type, COUNT(*), ROUND(SUM(ST_Length(geom))::numeric, 2)
    FROM public.roads
    GROUP BY road_type
    ORDER BY COUNT(*) DESC
""")

for row in results:
    road_type, count, total_length = row
    print(f"{road_type}: {count} segments, {total_length}m total")

---

Example 6: Create Auth Config Programmatically

from qgis.core import QgsApplication, QgsAuthMethodConfig

auth_mgr = QgsApplication.authManager()

# Build the auth config
config = QgsAuthMethodConfig()
config.setName("Production PostGIS")
config.setMethod("Basic")
config.setConfig("username", "gis_reader")
config.setConfig("password", "secure_password_here")

# Store -- the system assigns a unique ID
success, config = auth_mgr.storeAuthenticationConfig(config)
assert success, "Failed to store authentication config"

auth_id = config.id()
print(f"Auth config stored with ID: {auth_id}")

# Now use it in connections
from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("db.example.com", "5432", "production_gis", "", "")
uri.setAuthConfigId(auth_id)
uri.setDataSource("public", "parcels", "geom")

layer = QgsVectorLayer(uri.uri(False), "Parcels", "postgres")
assert layer.isValid()

---

Example 7: Export Layer to PostGIS via Processing

import processing
from qgis.core import QgsProject

# Get source layer from current project
source_layer = QgsProject.instance().mapLayersByName("Survey Points")[0]

result = processing.run("native:importintopostgis", {
    'INPUT': source_layer,
    'DATABASE': 'My PostGIS Server',
    'SCHEMA': 'survey_data',
    'TABLENAME': 'points_2024',
    'PRIMARY_KEY': 'id',
    'GEOMETRY_COLUMN': 'geom',
    'ENCODING': 'UTF-8',
    'OVERWRITE': True,
    'CREATEINDEX': True,
    'LOWERCASE_NAMES': True,
    'DROP_STRING_LENGTH': False,
    'FORCE_SINGLEPART': False
})

print("Export completed successfully")

---

Example 8: Export Layer with QgsVectorFileWriter

from qgis.core import (
    QgsVectorFileWriter, QgsCoordinateTransformContext, QgsProject
)

layer = QgsProject.instance().mapLayersByName("Roads")[0]

save_options = QgsVectorFileWriter.SaveVectorOptions()
save_options.driverName = "PostgreSQL"
save_options.layerName = "roads_backup"

# Use authcfg in the PG connection string
pg_uri = "PG:host=localhost port=5432 dbname=backup_db authcfg=pg_backup_auth"

error_code, error_msg, new_filename, new_layer = (
    QgsVectorFileWriter.writeAsVectorFormatV3(
        layer,
        pg_uri,
        QgsCoordinateTransformContext(),
        save_options
    )
)

if error_code != QgsVectorFileWriter.NoError:
    raise RuntimeError(f"Export failed ({error_code}): {error_msg}")

print(f"Exported to {new_filename}")

---

Example 9: Load PostGIS Raster

from qgis.core import (
    QgsProviderRegistry, QgsDataSourceUri, QgsRasterLayer, QgsProject
)

uri_config = {
    'dbname': 'elevation_db',
    'host': 'localhost',
    'port': '5432',
    'sslmode': QgsDataSourceUri.SslDisable,
    'authcfg': 'pg_raster_auth',
    'schema': 'terrain',
    'table': 'dem_tiles',
    'geometrycolumn': 'rast',
    'mode': '2'  # union all tiles into one layer
}

md = QgsProviderRegistry.instance().providerMetadata('postgresraster')
uri = QgsDataSourceUri(md.encodeUri(uri_config))

rlayer = QgsRasterLayer(uri.uri(False), "DEM", "postgresraster")
assert rlayer.isValid(), f"Raster load failed: {rlayer.error().message()}"

QgsProject.instance().addMapLayer(rlayer)
print(f"Raster size: {rlayer.width()}x{rlayer.height()}, bands: {rlayer.bandCount()}")

---

Example 10: Full Workflow -- Connect, Query, Load, Analyze

from qgis.core import (
    QgsProviderRegistry, QgsDataSourceUri, QgsVectorLayer,
    QgsProject, QgsFeatureRequest
)
import processing

# Step 1: Connect and discover
md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("City GIS Database")

# Step 2: Check what tables exist
tables = conn.tables("infrastructure")
spatial_tables = [t for t in tables if t.geometryColumn()]
print(f"Found {len(spatial_tables)} spatial tables in 'infrastructure' schema")

# Step 3: Load a specific table
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "city_gis", "", "")
uri.setAuthConfigId("city_gis_auth")
uri.setDataSource("infrastructure", "water_pipes", "geom")
uri.setUseEstimatedMetadata(True)

pipes = QgsVectorLayer(uri.uri(False), "Water Pipes", "postgres")
assert pipes.isValid()
QgsProject.instance().addMapLayer(pipes)

# Step 4: Run a spatial analysis
result = processing.run("native:buffer", {
    'INPUT': pipes,
    'DISTANCE': 50,
    'SEGMENTS': 16,
    'DISSOLVE': True,
    'OUTPUT': 'memory:'
})

buffer_layer = result['OUTPUT']
QgsProject.instance().addMapLayer(buffer_layer)

# Step 5: Export result back to PostGIS
processing.run("native:importintopostgis", {
    'INPUT': buffer_layer,
    'DATABASE': 'City GIS Database',
    'SCHEMA': 'analysis_results',
    'TABLENAME': 'pipe_buffer_50m',
    'PRIMARY_KEY': 'id',
    'GEOMETRY_COLUMN': 'geom',
    'OVERWRITE': True,
    'CREATEINDEX': True,
    'LOWERCASE_NAMES': True
})

print("Analysis complete -- buffer zones exported to PostGIS")

---

Example 11: List and Use Stored Connections

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")

# List all stored PostgreSQL connections
connections = md.connections()
for name, conn in connections.items():
    print(f"Connection: {name}")
    try:
        version = conn.executeSql("SELECT PostGIS_Version()")
        print(f"  PostGIS: {version[0][0]}")
    except Exception as e:
        print(f"  Error: {e}")

---

Example 12: Create Spatial Index and Vacuum

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("My PostGIS Server")

# Create spatial index if missing
if not conn.spatialIndexExists("public", "large_dataset", "geom"):
    conn.createSpatialIndex("public", "large_dataset")
    print("Spatial index created")

# Vacuum to reclaim space and update statistics
conn.vacuum("public", "large_dataset")
print("Vacuum complete")

API Signatures Reference (PostGIS / PyQGIS)

QgsDataSourceUri

Constructs and parses data source URIs for database providers.

class QgsDataSourceUri:
    def __init__(self, uri: str = "")

    # Connection parameters
    def setConnection(self, host: str, port: str, database: str,
                      username: str, password: str,
                      sslmode: SslMode = SslPrefer) -> None
    def host(self) -> str
    def port(self) -> str
    def database(self) -> str
    def username(self) -> str
    def password(self) -> str
    def sslMode(self) -> SslMode

    # Data source parameters
    def setDataSource(self, schema: str, table: str,
                      geometryColumn: str,
                      sql: str = "",
                      keyColumn: str = "") -> None
    def schema(self) -> str
    def table(self) -> str
    def geometryColumn(self) -> str
    def sql(self) -> str
    def keyColumn(self) -> str

    # Authentication
    def setAuthConfigId(self, authcfg: str) -> None
    def authConfigId(self) -> str

    # Metadata and performance
    def setUseEstimatedMetadata(self, flag: bool) -> None
    def useEstimatedMetadata(self) -> bool
    def setSrid(self, srid: str) -> None
    def srid(self) -> str
    def setWkbType(self, wkbType: QgsWkbTypes.Type) -> None
    def wkbType(self) -> QgsWkbTypes.Type
    def setKeyColumn(self, column: str) -> None

    # Generic parameters
    def setParam(self, key: str, value: str) -> None
    def param(self, key: str) -> str
    def params(self, key: str) -> list[str]
    def removeParam(self, key: str) -> int
    def hasParam(self, key: str) -> bool

    # URI output
    def uri(self, expandAuthConfig: bool = True) -> str
    def connectionInfo(self, expandAuthConfig: bool = True) -> str
    def quotedTablename(self) -> str

    # SSL modes
    class SslMode:
        SslPrefer = 0
        SslDisable = 1
        SslAllow = 2
        SslRequire = 3
        SslVerifyCa = 4
        SslVerifyFull = 5

Key rule: ALWAYS call uri(False) when passing to layer constructors to prevent credential exposure.

---

QgsAbstractDatabaseProviderConnection

Base class for database provider connections. Access via QgsProviderRegistry.

class QgsAbstractDatabaseProviderConnection:
    # Schema operations
    def schemas(self) -> list[str]
    def createSchema(self, name: str) -> None
    def dropSchema(self, name: str, force: bool = False) -> None

    # Table operations
    def tables(self, schema: str = "",
               flags: TableFlags = TableFlags()) -> list[QgsAbstractDatabaseProviderConnection.TableProperty]
    def table(self, schema: str, name: str) -> TableProperty
    def createVectorTable(self, schema: str, name: str,
                          fields: QgsFields, wkbType: QgsWkbTypes.Type,
                          srs: QgsCoordinateReferenceSystem,
                          overwrite: bool, options: dict = {}) -> None
    def dropVectorTable(self, schema: str, name: str) -> None
    def renameVectorTable(self, schema: str, name: str, newName: str) -> None

    # SQL execution
    def executeSql(self, sql: str, feedback: QgsFeedback = None) -> list[list]
    def execSql(self, sql: str, feedback: QgsFeedback = None) -> QueryResult

    # Vacuum
    def vacuum(self, schema: str, name: str) -> None

    # Spatial index
    def createSpatialIndex(self, schema: str, name: str,
                           options: dict = {}) -> None
    def spatialIndexExists(self, schema: str, name: str,
                           geometryColumn: str) -> bool
    def dropSpatialIndex(self, schema: str, name: str,
                         geometryColumn: str) -> None

---

QgsAbstractDatabaseProviderConnection.TableProperty

Describes a database table.

class TableProperty:
    def tableName(self) -> str
    def schema(self) -> str
    def geometryColumn(self) -> str
    def geometryColumnTypes(self) -> list[TableProperty.GeometryColumnType]
    def primaryKeyColumns(self) -> list[str]
    def geometryColumnCount(self) -> int
    def comment(self) -> str
    def flags(self) -> TableFlags
    def maxCoordinateDimensions(self) -> int

---

QgsProviderRegistry

Singleton registry for data provider access.

class QgsProviderRegistry:
    @staticmethod
    def instance() -> QgsProviderRegistry

    def providerMetadata(self, providerKey: str) -> QgsProviderMetadata
    def providerList(self) -> list[str]

---

QgsProviderMetadata

Metadata and connection factory for a data provider.

class QgsProviderMetadata:
    def createConnection(self, uri_or_name: str,
                         configuration: dict = {}) -> QgsAbstractDatabaseProviderConnection
    def connections(self, cached: bool = True) -> dict[str, QgsAbstractDatabaseProviderConnection]
    def deleteConnection(self, name: str) -> None
    def saveConnection(self, connection: QgsAbstractDatabaseProviderConnection,
                       name: str) -> None
    def encodeUri(self, parts: dict) -> str
    def decodeUri(self, uri: str) -> dict

---

QgsAuthManager

Manages the encrypted authentication database (qgis-auth.db).

class QgsAuthManager:
    # Access via QgsApplication.authManager()

    def storeAuthenticationConfig(self, config: QgsAuthMethodConfig) -> tuple[bool, QgsAuthMethodConfig]
    def loadAuthenticationConfig(self, authcfg: str, config: QgsAuthMethodConfig,
                                  full: bool = False) -> bool
    def removeAuthenticationConfig(self, authcfg: str) -> bool
    def configIds(self) -> list[str]
    def availableAuthMethodConfigs(self, dataprovider: str = "") -> dict[str, QgsAuthMethodConfig]
    def updateAuthenticationConfig(self, config: QgsAuthMethodConfig) -> bool
    def authenticationDbPath(self) -> str
    def masterPasswordIsSet(self) -> bool
    def setMasterPassword(self, password: str, verify: bool = True) -> bool

---

QgsAuthMethodConfig

Configuration object for a single authentication entry.

class QgsAuthMethodConfig:
    def __init__(self, method: str = "", version: int = 0)

    def id(self) -> str
    def setId(self, id: str) -> None
    def name(self) -> str
    def setName(self, name: str) -> None
    def method(self) -> str
    def setMethod(self, method: str) -> None
    def config(self, key: str, defaultValue: str = "") -> str
    def setConfig(self, key: str, value: str) -> None
    def configMap(self) -> dict[str, str]
    def isValid(self, validateId: bool = False) -> bool

---

QgsVectorFileWriter (PostGIS Export)

class QgsVectorFileWriter:
    class SaveVectorOptions:
        driverName: str           # "PostgreSQL" for PostGIS
        layerName: str            # target table name
        actionOnExistingFile: int # 0=CreateOrOverwrite, 1=CreateNewFile, 2=AppendToLayer

    @staticmethod
    def writeAsVectorFormatV3(
        layer: QgsVectorLayer,
        fileName: str,           # PG connection string for PostGIS
        transformContext: QgsCoordinateTransformContext,
        options: SaveVectorOptions
    ) -> tuple[WriterError, str, str, str]

    class WriterError:
        NoError = 0
        ErrDriverNotFound = 1
        ErrCreateDataSource = 2
        ErrCreateLayer = 3
        ErrAttributeTypeUnsupported = 4
        ErrAttributeCreationFailed = 5
        ErrProjection = 6
        ErrFeatureWriteFailed = 7
        ErrInvalidLayer = 8
        Canceled = 9

---

Processing Algorithm: native:importintopostgis