Multiple geometry types in one column not recognized

Question

I often use PostGIS tables with a geometry column of type "Geometry" containing varying geometry types e.g. polygons and points. When opening the table in QGIS the Connections dialogue lists the table once for each different geometry type as expected. Today I converted a PostGIS table from MultiLineString to Geometry and added some point and polygon objects to the table. But QGIS does not recognize the change and has only one entry for the table in the Connections dialog with a Spatial Type of LineString. However, in PostGIS, if I query:

select distinct st_geometrytype(wkb_geometry)
from tracts_test;

I get:

"ST_LineString"
"ST_Polygon"
"ST_Point"

as expected.

I've tried adding and removing the spatial index, adding and removing the primary key, and creating a new table with create NEW_TABLE as select * from OLD_TABLE; but in no case does QGIS see any geometry type other than LineString. What else should I be doing, or where else should I be looking, coerce QGIS into seeing the other geometry types in the table?

Answer 1

I'll answer my own question but credit goes to user30184 for giving me a couple clues. The problem seems to be that the first few thousand rows in my table are LineStrings and apparently QGIS isn't examining all of the rows and finding the other geometry types in the table.

The solution, at least in QGIS 3.18.2, is to check the "Don't resolve type of unrestricted columns (GEOMETRY)" in the PostGIS Connections options.

After reconnecting to the Postgres database the Connections list will now have a "Select" in the "Spatial Type" column for tables with a spatial type of Geometry as shown below.

You can only pick one geometry type at a time and add it to your map. To add another geometry type from the same table you must reconnect to the database to get the Select list again.

I opened a issue on github.

Answer 2

Perhaps the only thing that you need to do is to refresh the connection by pressing the Connect button again.

This was my test case:

CREATE TABLE geometrytest (id integer, geom geometry(MultiLineString));
INSERT INTO geometrytest values (1,ST_GeomFromText('MultiLineString ((0 0,3 3))'));
ALTER TABLE geometrytest ALTER COLUMN geom type geometry(Geometry);
INSERT INTO geometrytest values (2,ST_GeomFromText('POINT (1 2)'));
INSERT INTO geometrytest values (3,ST_GeomFromText('LINESTRING (1 2, 0 2)'));
INSERT INTO geometrytest values (4,ST_GeomFromText('POLYGON ((1 2, 1 3, 2 3, 1 2))'));

I read the data first with QGIS after line 2 when the table was still MultiLineString. Then I changed the geometry type and added three more geometries into the table. When I added this layer again into QGIS, without adding any data from other connections in between, QGIS still considered that the table was MultiLineString. Obviously QGIS is caching the details of the tables for speeding up things. Pressing the Connect button forces QGIS to read and interpret the metadata tables again and the different geometry types appear on the list.