Insert empty VIEWS into QGIS from PostgreSQL using PyQGIS

Question

The Aim:
I am trying to insert a number of empty VIEWS into QGIS using PyQGIS. These VIEWS have been created and stored in a PostgreSQL database.

I want to also ensure i can successfully re-open the project at a later date whilst some of the layers are still empty or have become empty. These layers can at times be empty depending on what has been happening recently in the database.

The Problem:
QGIS does not like it when these layers are empty on inserting them, or when attempting to re-open the project. It either fails to insert the empty layer, or it informs the user that they are 'bad layers' and need to be deleted in order to continue. The layers can be inserted successfully when they contain data.

Python code used:
Note: "PlatformID" is the primary key.

uri = QgsDataSourceUri()
uri.setConnection(host, port, db_name, user, password)
uri.setDataSource("Data", "FieldOfViewIndex", "geom", "", "PlatformID")
vlayer = QgsVectorLayer(uri.uri(False), "FieldOfViewIndex", "postgres") 
QgsProject.instance().addMapLayer(vlayer)

Common error messages:

WARNING Geometry type and srid for empty column geom of "Data"."FieldOfViewIndex" undefined.

WARNING invalid PostgreSQL layer

In the PostgreSQL geometry_columns VIEW, their SRID is 0 and the geometry type is GEOMETRY.

• J. Monticolo: Have you created the geom field with SELECT AddGeometryColumn (...) for explicit geometry and srid declaration ? doc.

Answer 1

You can force the geometry type and projection in the view definition by casting the geometry:

create view tv as 
 select geom::geometry(POINT, 4326)
 from myTable;
jgtest=> \d tv
 View "public.tv"
 Column | Type | Collation | Nullable | Default
------------+----------------------+-----------+----------+---------
 geom | geometry(Point,4326) | | |

• Demus: Perfect. This has done it thank you. I didn't expect the answer to be so simple.