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.

HeyOverThere · Answer

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 by HeyOverThere on March 14, 2021

Luís de Sousa · Answer

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 Answers

Add your own answers!

Ask a Question