PostGIS-syntax: export query-result to QGIS / Shapefile

Question

Using PostGIS: syntax-basics for function, I was able to solve a long-standing problem by creating a geometry, using a SQL-query with PostGIS. However, I want to use the created geometry in QGIS and have problems exporting it.

QGIS 3.8.2. Zanzibar, Windows 10, PostgreSQL 11.5.1., PostGIS 2.5.2

The query I used was as follows - it creates a Medial Axis of a Polygon (Shapefile bufferrail imported to PostGIS with shp2pgsql-gui.exe):

Select ST_ApproximateMedialAxis(geom) from public.bufferrail;

The result is shown in the Geometry-viewer-tab in pgAdmin4 and it looks like what I wanted to accomplish. However, I failed to export this result into QGIS. What I already tried:

1.) Using database-manager in QGIS, when I perform the query there and try to load the result as a new layer (load-Button), I get an error-message - see the protocol-entry (in german, unfortunately, it basically says Invalid PostgreSQL-Layer and WARNING: incorrect query):

Ungültiger PostgreSQL-Layer
WARNING    Fehlerhafte Abfrage: SELECT "" FROM (SELECT row_number() over ()
AS _uid_,* FROM (SELECT ST_ApproximateMedialAxis(geom) from public.bufferrail
             ) AS _subq_1_
             ) AS "subQuery_0" LIMIT 0 ergab 7 [FEHLER: Bezeichner in Anführungszeichen hat Länge
null bei »""«
             LINE 1: SELECT "" FROM (SELECT row_number() over () AS _uid_,* FROM ...
              ^
             ]
2019-08-25T23:47:57     WARNING    Ungültiger PostgreSQL-Layer

2.) Again in QGIS database-manager, I created a view from the query result. When I try to add this view to map (always from QGIS DB manager - right-cick on the view in pubic), QGIS always crashes.

3.) Still in QGIS database-manager, I try to export to file (Geopackage) - however, when I add the saved file as a layer to QGIS, an empty layer with no geometries is added.

4.) In pgAdmin 4: I run the SQL-query mentioned above and than click Download as CSV. I get a text-file with cryptic content (when opened in an editor). So no idea how to use it.

5.) I tried to google export shp from PostGIS and tried to use pgsql2shp - however, all of the many expressions to include pgsql2shp in my query failed - again, no idea how correct syntax works here.

6.) By the way: is there no way to use a simple GUI-method for export - similar to import shp to PostGIS with shp2pgsql-gui.exe? I tried the Export-tab in the GUI there, but export works only with tables. So probably a solution would be to convert the result of my query to a table - but unfortunately, again all my efforts failed - wrong syntax, again!

To come to my question: I don't care much how I can export the result form a relativele simple query in PostGIS to opening it in QGIS. Probably solution 5 is the most promising: so if someone could help with correct syntax, connection my original query and the export-function with pgsql2shp. But any other solution is good, too.

Answer 1

There was a comment to my question that provided an answer to my problem, but it disappeared meanwhile - however, here is the solution, proposed by user MrXsquared:

I simply have to add a unique ID field - I included the fid from my input-layer bufferrail:

SELECT fid, ST_ApproximateMedialAxis(geom) from public.bufferrail;

With this result, I could add it as a layer in QGIS (solution 2 from above). Still, exporting that layer created another issue with unique ID, but I simply had to select all objects and insert it in an already existing line-layer.

For background-information, more details on QGIS returns “Invalid Layer” message when trying to add PostGIS view?