Unusable QGIS layer from PostGIS

Question

I am developing a QGIS plugin. I need to load some polygons from a table in PostGIS. This table is defined with this SQL query :

CREATE TABLE cad.polygon(
    id_polygon serial NOT NULL,
    geometry geometry NOT NULL,
    id_plan integer NOT NULL,
    manually boolean NOT NULL,
    creation_date date NOT NULL,

    CONSTRAINT polygon_pkey PRIMARY KEY(id_polygon),
    CONSTRAINT fk           FOREIGN KEY(id_plan) REFERENCES cad.plan(id_plan)
) WITH (OIDS = FALSE) TABLESPACE pg_default;

and contains 163 entities with two geometry types (curve polygon and polygon). I wrote a Python script to get these polygons in QGIS :

uri = QgsDataSourceUri()

# Connect to the database on the local host
uri.setConnection(
    "localhost",
    "5432",
    "dbname",
    "username",
    "*****"
)

# SELECT * FROM cad.polygon WHERE id_plan = <plan ID>
uri.setDataSource(
    "cad",
    "polygon",
    "geometry",    # Column name for the geometry
    "id_plan = 2"  # whatever the plan ID
)

uri.setKeyColumn("id_polygon")
uri.setWkbType(QgsWkbTypes.Polygon)

# Create a new QGIS vector layer
layer = QgsVectorLayer(uri.uri(False), "polygons", "postgres")
QgsProject.instance().addMapLayer(layer)

When I run this script, near the layer generated in the legend, there is this message :

Unusable layer : The data source of this layer has not been found. Click to choose a new data source.

I don't understand where this error comes from and how to fix it.

Answer 1

The geometry column must not be defined in the table definition. In the table definition you just define all other columns besides the 1 or n geometry columns.

To define these you need the PostGIS AddGeometryColumn function, refer to https://postgis.net/docs/AddGeometryColumn.html

In your case:

CREATE TABLE cad.polygon(
    id_polygon serial NOT NULL,
    id_plan integer NOT NULL,
    manually boolean NOT NULL,
    creation_date date NOT NULL,

    CONSTRAINT polygon_pkey PRIMARY KEY(id_polygon),
    CONSTRAINT fk           FOREIGN KEY(id_plan) REFERENCES cad.plan(id_plan)
) WITH (OIDS = FALSE) TABLESPACE pg_default;

SELECT AddGeometryColumn ('cad', 'polygon', 'geometry', 4326, 'MULTIPOLYGON', 2);

4326 is the srid of the geometry wich you might replace if applies.