Duplicate entries in table list when adding PostGIS layer in QGIS

Question

When adding PostGIS layer in QGIS (version does not matter) i observe duplicate entries in the list as can be seen below (e.g. ax_gebaeude, ax_flurstueck, ax_gehoelz...), one with a valid geometry, the other(s) with a warning and a 'select geometry type' hint.

Adding the ones with valid geometry to the project causes no issue, but i am not sure what this means.

Can anyone clarify this behaviour?

Added/Edit:

geometry_columns view:

and everything's fine in browser panel:

and another behaviour in the DB Manager plugin. For geometry_types 'GEOMETRY' are marked with a '?' and it says 'no entry in geometry_columns' (wich is definitively not the case) but preview is no problem:

Answer 1

QGIS is happiest with PostGIS layers when they have complete information in the geometry_columns view. To achieve this state, you have to make sure the geometry columns are created with useful metadata. This kind of issue usually comes up with tables created using CREATE TABLE AS SELECT ... style queries.

Try something like this:

CREATE TABLE foo AS 
SELECT ST_Union(geom)::Geometry(Point, 4326), gid 
FROM bar;

Note that in defining the geometry output of the query, we are very specifically telling the system what kind of geometry to expect. This will get stored in the system catalogues and then reflected in the geometry_columns table, which will then make QGIS happy.

You can also update columns after the fact with an ALTER TABLE query:

ALTER TABLE foo
ALTER COLUMN geom type Geometry(Point, 4326) USING geom::Geometry(Point, 4326)

Note we also included a primary key, gid. This is something you might have to add by hand afterwards, using an ALTER TABLE to define it as a primary key.

Answer 2

The list shows the available geometry types. ax_flurstueck doesn't have a geometry type constraint. The additional line in the list enables you to add the layer as point or line layer so you can also add points or lines.