PyQGIS: Querying PostGIS Table

Question

I am trying to run a query on a PostGIS table via PyQGIS (QGIS3.16-Hannover installed on an Ubuntu 20.04LTS Desktop). Everything works fine if I SELECT ALL FIELDS using the wildcard pattern (*) according to the code below:

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "dbname", "username", "password")
nb = 1050130
fields = '*'
sql ='''(SELECT {} FROM montebelodosul.cadastro_urbano_montebelodosul_p WHERE numero_cadastro = {})'''.format(fields,nb)
uri.setDataSource('', f'({sql})', 'geom', '', 'id')
# add the layer to the canvas
pg_layer = QgsVectorLayer(uri.uri(False), "queryLayer", "postgres")
if not pg_layer.isValid():
    print ("Table %s did not load" % pg_layer.name())
QgsProject.instance().addMapLayer(pg_layer)

However, changing the sql to SELECT a specific table field such as id_ruas, as shown below:

uri = QgsDataSourceUri()
    uri.setConnection("localhost", "5432", "dbname", "username", "password")
    nb = 1050130
    fields = 'id_ruas'
    sql ='''(SELECT {} FROM montebelodosul.cadastro_urbano_montebelodosul_p WHERE numero_cadastro = {})'''.format(fields,nb)
    uri.setDataSource('', f'({sql})', 'geom', '', 'id')
    # add the layer to the canvas
    pg_layer = QgsVectorLayer(uri.uri(False), "queryLayer", "postgres")
    if not pg_layer.isValid():
        print ("Table %s did not load" % pg_layer.name())
    QgsProject.instance().addMapLayer(pg_layer)

I get the following error message:

Erroneous query: SELECT "geom" FROM ((SELECT id_ruas FROM montebelodosul.cadastro_urbano_montebelodosul_p WHERE numero_cadastro = 1050130)) AS "subQuery_0" LIMIT 0 returned 7 [ERROR: column "geom" does not exist LINE 1: SELECT "geom" FROM ((SELECT id_ruas FROM montebelodosul.cada...

How do I fix this?

Answer 1

As you have specified the geometry column (geom) in uri.setDataSource(), you need to select the geom column along with the id_ruas column.

nb = 1050130
fields = 'id_ruas, geom'   -- include geom in the selection
sql ='''(SELECT {} FROM montebelodosul.cadastro_urbano_montebelodosul_p WHERE numero_cadastro = {})'''.format(fields,nb)

If id_ruas is not the primary key, you will need to select that also.