Bug report #13670: DBManager selects wrong field as key when loading view from PostGIS - QGIS Application - QGIS Issue Tracking

Bug report #13670

DBManager selects wrong field as key when loading view from PostGIS

Added by Sebastian Dietrich over 9 years ago. Updated almost 6 years ago.

Status:	Closed
Priority:	Normal
Assignee:	-
Category:	DB Manager
Affected QGIS version:	master	Regression?:	No
Operating System:		Easy fix?:	No
Pull Request or Patch supplied:	No	Resolution:	end of life
Crashes QGIS or corrupts data:	No	Copied to github as #:	21704

Description

When loading a view with DBManager the first numeric column is selected as the key. If the data in that column is not unique the addition of the layer fails.
If it is unique in the first place but later becomes non-unique, the layer fails to load correctly when opening the project or layer definition file.

Related issues

History

#1 Updated by Sebastian Dietrich over 9 years ago

To reproduce:

Create a view with the first numeric column not being unique:

CREATE OR REPLACE VIEW "TestViewFail" AS 
SELECT
   1 AS "nonID",
   generate_series(1, 2) AS "ID"

Create the same view with the columns swapped:

CREATE OR REPLACE VIEW "TestViewPass" AS 
SELECT
   generate_series(1, 2) AS "ID",
   1 AS "nonID"

Try to add both views from DBManager. Note that the addition of the first view fails while the addition of the second one succeeds.

#2 Updated by Sebastian Dietrich over 9 years ago

Assignee set to Giuseppe Sucameli

This is related to #13341, where DBManager fails to load a view with a non-numeric primary key.

I think the best solution - for both tickets - would be to have the user select the columns that make up the primary key for the view. Pretty much the same way it is already done when adding a query from DBManager or when adding a view from the Add PostGIS layer dialog.