QGIS DBManager: Error Adding Layer

Question

Background: I am running QGIS 3.10.1, PostgreSQL 12.1 and PostGIS 3.0.0 on Windows 10. Using this environment, I am trying to run a simple query in DB Manager:

SELECT polygon('((10,20),(25,30),(30, 30),(10,20))')::geometry
UNION ALL
SELECT path('(1,21), (5,15), (9,20), (12,5)')::geometry
UNION ALL
SELECT box('(10, 21)'::point, '(16,10)'::point)::polygon::geometry
UNION ALL
SELECT circle('(20,10)'::point, 3)::polygon::geometry;

When I execute this query in DB Manager, it gives me the expected results:

I want to visualize the result of this query, so I try to save it as a layer, like so:

However, when I click the Load button, it gives me the following error:

When I look at the logs in QGIS:

Something is Missing

It is immediately apparent that the generated query is missing something inside the highlighted double quotes. What is the missing element? Is it the unique identifier, such as the _UID_ that I see in related posts on this same or similar topic?

QGIS Setting?

If so, is there a setting somewhere within QGIS where I can set this variable? Where is it?

I have seen multiple posts around this same issue, but none have pointed me to where I can set this variable correctly. It could be that version 3.10 is just too new for this.

An Experiment

As a little experiment, I created a table in the database with the following code:

CREATE TABLE ch02.geom_shapes AS (
  SELECT polygon('((10,20),(25,30),(30, 30),(10,20))')::geometry
  UNION ALL
  SELECT path('(1,21), (5,15), (9,20), (12,5)')::geometry
  UNION ALL
  SELECT box('(10, 21)'::point, '(16,10)'::point)::polygon::geometry
  UNION ALL
  SELECT circle('(20,10)'::point, 3)::polygon::geometry
);

When I open this table directly in QGIS, I get the following:

No errors!

This is precisely the result that I was expecting to see. I do hope that someone knows how to get around the error that occurs when trying to create the layer from DB Manager.

Answer 1

Actually,

• the query syntax is not wrong (that's why the query executes successfully)
• QGIS doesn't mind mind if you don't have a uid column (it simply creates one, as you can see)
• it's not necessary to alias the geometry column (you can simply choose the appropriate identifier)

The issue is the mixed geometry types; QGIS is not designed to handle those within the same layer!

Edit:
Creating a mixed geometry table will result in QGIS listing each geometry type as a separate View, to load only a distinct geometry type from the base table!

Each of your geometries will load successfully when loaded individually, e.g.:

SELECT polygon('((10,20),(25,30),(30, 30),(10,20))')::GEOMETRY;

or, simply if you leave out the LineString (path())!

---

However, it is unusual that you are using the PostgreSQL geometric types rather than the PostGIS geometry constructors; I'd suggest to use the appropriate ST functions, and set the CRS;

SELECT ST_GeometryFromText('POLYGON((10 20, 25 30, 30 30, 10 20))', <SRID>) AS geom;
SELECT ST_SetSRID('POLYGON((10 20, 25 30, 30 30, 10 20))'::GEOMETRY, <SRID>) AS geom;

or

SELECT ST_GeometryFromText('LINESTRING(1 21, 5 15, 9 20, 12 5)', <SRID>) AS geom; 
SELECT ST_SetSRID('LINESTRING(1 21, 5 15, 9 20, 12 5)'::GEOMETRY, <SRID>) AS geom;

or

SELECT ST_SetSRID(ST_Buffer(ST_MakePoint(20 10), 3), <SRID>) AS geom;

If you want to stick to using the PostgreSQL types as constructors, at least set the CRS appropriately:

SELECT ST_SetSRID(Polygon('((10,20),(25,30),(30, 30),(10,20))')::GEOMETRY, <SRID>) AS geom;