I'm trying to load (by mean of PyQGIS) a layer from PostGIS based on query involving a inner join with a non-spatial table. When I run the query in pgAdmin I get the desired output but when I integrate that query in my PyQGIS code I get an error message from QGIS saying that the primary key of the layer is not unique.
How can I load the layer since it is the result of a join between two table and obviously doesn't have a primary key ? (DB manager can load table without unique primary key, how can I do that with PyQGIS?)
Code sample:
self.sql_test = """
(SELECT a.field1, b.field1, a.field2 FROM (SELECT w.field1, w.geom, ST_INTERSECTION(v.geom, w.geom)
FROM schema1."table1" v, schema2."table1" w
WHERE ST_INTERSECTS(v.geom, w.geom) AND v.table3 LIKE 'AR457') as a
INNER JOIN (select * from schema2.table4)as b on a.field1=b.field1)
"""
self.uri.setDataSource("", self.sql_test, "geom", "", "par_id")
self.test= QgsVectorLayer(self.uri.uri(), "test", "postgres")
if not self.test.isValid:
print('Chargement de la couche: echec !')
else:
QgsProject.instance().addMapLayer(self.test)
row_numbercan generate unintended results if feature "1" is filtered by a query envelope and is actually the fifth record is the return results. Then there's the possibility that the return order changes depending on the whim of the optimizer when an ORDER BY is not provided. Be careful withrow_number.ctidattribute, which is an internal system column that shows the physical location of the row version within its table. postgresql.org/docs/current/ddl-system-columns.html and postgresql.org/docs/current/datatype-oid.html A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its tableorder byclause should be mandatory here (ROW_NUMBER() OVER(ORDER BY tbl1.id, tbl2.id, tbl2.whatever_to_make_row_order_unique)). It is not only a matter of purposely using the field later, but of QGIS using it in your back. For instance, the order the features are returned could be different when displaying the geometries and when loading the attribute table, effectively disconnecting the geometries from their attributes.