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
 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