Bug report #13670

DBManager selects wrong field as key when loading view from PostGIS

Added by Sebastian Dietrich over 8 years ago. Updated about 5 years ago.

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


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


#1 Updated by Sebastian Dietrich over 8 years ago

To reproduce:
  • Create a view with the first numeric column not being unique:
       1 AS "nonID",
       generate_series(1, 2) AS "ID" 
  • Create the same view with the columns swapped:
       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 8 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 about 7 years ago

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

#4 Updated by Jürgen Fischer over 5 years ago

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

#5 Updated by Giovanni Manghi about 5 years ago

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

Also available in: Atom PDF