Populate_Geometry_Columns does not work with materialized view

Question

I have created materialized view with Points as ... ST_SetSRID(ST_MakePoint( longitude,latitude), 4326) as geom... column data. The table kind of works, I can create spatial index which works etc, but the geometry_columns view shows srid=0 and type=GEOMETRY. This is major issue for QGIS which seems to try to full scan the table to find out true geometry type. And that takes too much time as my view is quite large (over 500M rows).

Next I tried to fix the metadata with select Populate_Geometry_Columns('public.mymaterializedview'::regclass) , but this returns immediately '0' instead of '1' what I normally get with real tables. And the geometry_columns does not get changed.

I tried also setting column type with ALTER TABLE public.mymaterializedview ALTER COLUMN geom type geometry(Point, 4326) USING ST_SetSRID(geom, 4326); but this gives [42809] ERROR: "mymaterializedview" is not a table, composite type, or foreign table which is not very helpful for me.

The DB is GCP managed Cloud SQL DB, i.e. Postgres 9.6 and PostGIS full version is currently POSTGIS="2.3.0 r15146" GEOS="3.5.0-CAPI-1.9.0 4392" PROJ="Rel. 6.0.0, March 1st, 2019" GDAL="GDAL 2.4.0dev, released 2018/99/99" LIBXML="2.9.1" LIBJSON="0.12.1" RASTER

Answer 1

You can add a cast when you create the geometry

CREATE MATERIALIZED VIEW public.mv_test AS
    SELECT 
    adr.adr_id,
    ST_SetSRID(ST_MakePoint( adr.adr_longitude,adr.adr_latitude), 4326)::geometry(point,4326) as geom
 FROM  public.adresses adr; 

The geometry type and SRID will be properly reported in geometry_column

select * from geometry_columns where f_table_name = 'mv_test';
 f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type
-----------------+----------------+--------------+-------------------+-----------------+------+-------
 testdb          | public         | mv_test      | geom              |               2 | 4326 | POINT