Duplicate PostGIS view entries in QGIS

Question

I've got a table called "MargamParkMap" - which is made up of polygons. One of the attributes in the table is "authors".

I've created a view with the aim of merging the polygons by author. What I've got is this:

SELECT 
row_number() OVER (ORDER BY "MargamParkMap".author) AS qgisid,
st_union(ST_MakeValid(ST_Multi("MargamParkMap".geom))) AS geom,
"MargamParkMap".author
FROM "MargamPark"."MargamParkMap"

GROUP BY "MargamParkMap".author;

So - row_number() is generating a new column with a unique ID, and the geom column is made up of the union of the original geom column.

If I run the query on it's own, it returns a lovely little table with all the right stuff in.

However, in QGIS - when I go to the view there are now 2 of them:

that is a really small picture...

Anyways - one has the correct geometry type, multipolygon, and SRID, the other doesn't...Is there any way of only showing the correct view?

(geometry_columns view shows the srid as 0, and type as geometry)

Also, is there a way of automatically selecting the Feature ID as the qgisid?

(PostGIS version 2.3.0, QGIS version 2.16.3)

Answer 1

From the postgres-side it's not possible to solve this, as postgres doesn't support primary keys on views. Those keys are used by QGIS to determine the feature id column.

You could export the data into a new table like this

SELECT 
row_number() OVER (ORDER BY "MargamParkMap".author) AS qgisid,
st_union(ST_MakeValid(ST_Multi("MargamParkMap".geom))) AS geom,
"MargamParkMap".author
FROM "MargamPark"."MargamParkMap"
INTO
newtable
GROUP BY "MargamParkMap".author;

ALTER TABLE newtable
  ADD PRIMARY KEY (qgisid);