QGIS returns "Invalid Layer" message when trying to add PostGIS view?

Question

I'm new to Postgis and QGIS. I created a view defined as

CREATE OR REPLACE VIEW pupr.vstudentmuni AS 
 SELECT m.id AS munid, m.name AS muname, m.geom, s.code, 
    (s.name::text || ' '::text) || s.lastname::text AS fullname
   FROM pupr.municipio m, pupr.student_location l, pupr.student s
  WHERE st_within(l.geom, m.geom) AND s.code = l.code;

in PostgreSQL. Trying to add this view to QGIS returns the following error...

"Invalid layer"..."sql= is an invalid layer and cannot be loaded."

Not sure what the problem is. The table is mentioned in the geommetry_columns table in PostGIS, but I can see a message entry in QGIS that says "No key field for given view". I'm using PostgreSQL 9.2 and QGIS 1.8.0.

The problem seem to be related to the unique key field, you must have one. I don't know your data, maybe you could explain a bit more about what you want to achieve with your query. How are you opening the Layer in QGIS? Did you choose a key?
From what you described, only s.code can be use as unique key. Also, your municipio geometries will be repeated several times for each student, is that what you want? — Alexandre Neto, Jan 22 '13 at 14:49
possible duplicate of How to make a spatial view in PostGIS and add it as a layer in QGIS? — n1000, May 19 '15 at 09:42

score 9 · Answer 1 · answered Jan 22 '13 at 14:38

9

When you add a view from PostGIS to QGIS you have to specify the unique ID field. Off to the right of the layer name will be a drop down where you can specify which field to use. If you don't have a unique ID field you can create a fake ID field using the row_number() window function in the query that creates your view.

answered Jan 22 '13 at 14:38

HeyOverThere

7,861
1
27
41

1

This question is related and gives more detail on selecting your unique ID field in the QGIS gui. – RyanKDalton Jan 22 '13 at 15:52
1

And this question will give you more details on how to create a view with a unique id. – RyanKDalton Jan 22 '13 at 15:53
Thanks all for the replies. Problem solved using the suggested function. – ufeliciano Jan 23 '13 at 14:00

Luís de Sousa · Answer 2 · 2021-01-10T11:27:58.683

Today I bumped into this error with a view created from a join between tables. It ended up having two unique fields that upset QGis. Initially the view was like:

CREATE OR REPLACE VIEW my_view AS
SELECT *
  FROM more_data m,
       node n   
 WHERE m.id_node = n.id;

This way there were two primary key fields in the view. Instead, I had to specify the resulting fields, in order to end up a single field candidate for unique identifier:

CREATE OR REPLACE VIEW my_view AS
SELECT n.id,
       n.name,
       n.geom,
       m.type,
       m.other_stuff,
       m.more_stuff
  FROM more_data m,
       node n   
 WHERE m.id_node = n.id;

QGIS returns "Invalid Layer" message when trying to add PostGIS view?

2 Answers2

Linked