Bug report #13670

DBManager selects wrong field as key when loading view from PostGIS

Added by Sebastian Dietrich almost 5 years ago. Updated over 1 year 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

Related to QGIS Application - Bug report #13341: DBManager doesn't import view from table with non numeric... Closed 2015-09-11

History

#1 Updated by Sebastian Dietrich almost 5 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 almost 5 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.

#3 Updated by Giovanni Manghi over 3 years ago

  • Easy fix? set to No
  • Regression? set to No

#4 Updated by Jürgen Fischer about 2 years ago

  • Assignee deleted (Giuseppe Sucameli)
  • Description updated (diff)

#5 Updated by Giovanni Manghi over 1 year ago

  • Resolution set to end of life
  • Status changed from Open to Closed

Also available in: Atom PDF