Bug report #15226
Primary key issue when copying a table by Drag&Drop
|Affected QGIS version:||2.16.3||Regression?:||No|
|Operating System:||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:||fixed/implemented|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||23163|
When copying a table (PostGIS) in the 'DB Manager' by D&D, the name of the primary key is placed in quotes (e.g. "gid"). As a result, there is a "gid" and a gid column in the new table.
#4 Updated by Sandro Santilli over 3 years ago
- Status changed from Open to Feedback
- Assignee set to Sandro Santilli
I've tried dragging the qgis_test.base_table_good table (part of testsuite in qgis_test database) into a new schema and it worked fine with 2.16 branch (2.16.3), 2.14 branch (2.14.7) and master_2 branch (2.17).
#6 Updated by Sandro Santilli over 3 years ago
Interesting, can you inspect the two tables (the one which works and the one which doesn't) with the "psql" command-line database tool ? Can it be the "gid" field is really encoded differently ? Or, can you reproduce after dumping and reloading that table ? Can you spot any other difference between the two tables ? Does it always happen on that table, no matter the order in which you copy them ?
#8 Updated by Sandro Santilli over 3 years ago
- Affected QGIS version changed from master to 2.16.3
- Target version set to Version 2.18
- Status changed from Feedback to In Progress
Yes, I can reproduce with the table you provided. And not with other tables. I'm on it.
2.14.7 is not affected, 2.16.3 and 2.17 are.
#11 Updated by Jürgen Fischer over 3 years ago
Sandro Santilli wrote:
Jurgen, once again this issue is about properly defining semantic for some fields.
In this case is the "keyColumn()" return from QgsDatasourceUri.
Is that supposed to contain quoted or unquoted names ?
the postgres provider handles quoted and unquoted content from keyColumn() - dbmanager should do too.
#12 Updated by Sandro Santilli over 3 years ago
On further research, I found that we get quoted identifiers in the url when the primary key type is a FidMap. This happens for multi-column keys or for non-integer keys. In both these cases, even with QGIS 2.14, we get quoted attributes in the QgsDatasourceUri keyColumn() return.
In all these cases, the DBManager drag & drop operation results in a target table having quotes in the key column names.
Even in QGIS 2.14.
I'll see how DBManager should deal with quotes, but given the above I guess the only sane way to deal with quotes would be knowing if the quotes were added or were part of the actual field name, so somehow the "FidMap" nature should be exposed.
or we should always quote the columns ?
BTW, I'm not sure it's DBManager or the provider's "CreateEmptyLayer" being responsible of parsing the URL.
Will keep looking.
#14 Updated by Sandro Santilli over 3 years ago
So with PR https://github.com/qgis/QGIS/pull/3599 I've made PostgreSQL layer importer (createEmptyLayer) support quoted identifiers in Datasource URI -- for backward compatibility with projects created #13710 was fixed (that ticket lacks a target version) -- and I've reverted the commit which did add quoted identifiers in Datasource URI as they do seem redundant and inconsistent (I did test that #13710 isn't back).
In its current state, the code in the PR not only fixes this bug but also adds support for Drag&Dropp'ing tables with multi-column keys (including those with mixed-cased-colum names)