Bug report #11550
Import into PostGIS doesn't check for field named "id"
|Affected QGIS version:||2.6.0||Regression?:||No|
|Operating System:||Windows 64-bit||Easy fix?:||No|
|Pull Request or Patch supplied:||No||Resolution:|
|Crashes QGIS or corrupts data:||No||Copied to github as #:||19820|
I am often given data that already contains a field named "ID". In many cases, this field is no longer a unique identifier. When I run "Import into PostGIS" from the processing toolbox and leave the primary key field to [not set], I receive a PostGIS error for violating a unique value constraint. It appears that the tool creates a column called "id" to use as a primary key, but doesn't check to see if I already have a field with that name in my data.
Expected behavior would be that the tool automatically tries a new name (id1, id2, etc.) until it finds one that isn't already present. Alternatively, the tool should at least give me an option to create my own name for the primary key field so that I can avoid the issue.
#1 Updated by Giovanni Manghi over 5 years ago
- Status changed from Open to Feedback
I cannot confirm using the latest version of the Processing toolbox. If "id" exist in the input vector then a "id_0" column is created in the postgis table.
Please check if you have any "processing" folder inside .qgis2/python/plugins, if yes then delete it, restart qgis and try again.
#2 Updated by Spencer Gardner over 5 years ago
There is no "processing" folder in .qgis2/python/plugins but keep in mind that I'm on Windows - not sure if that makes a difference. I did see a "processing" folder at .qgis2/processing. I tried renaming that folder and running the tool again but had the same result (and a new processing folder was created). I found processing.log there - here is an output from my latest attempt:
ALGORITHM|Wed Nov 05 2014 09:03:44|processing.runalg("qgis:importintopostgis","C:/TEMPGIS/TDG_Project_Selection_Data.gdb|layername=TMA_DemProfile",7,"scratch","err_test","None","geom",True,True,True,False)
ERROR|Wed Nov 05 2014 09:03:45|Error importing to PostGIS|Feature write errors:|Creation error for features from #0 to #0. Provider errors was: |PostGIS error while adding features: ERROR: duplicate key value violates unique constraint "err_test_pkey"|DETAIL: Key (id)=(1132) already exists.||Creation error for features from #0 to #0. Provider errors was: |PostGIS error while adding features: ERROR: duplicate key value violates unique constraint "err_test_pkey"|DETAIL: Key (id)=(1132) already exists.||Only 0 of 225 features written.
When I look at my database, the table has been created and the "id" field was made the primary key, which resulted in the duplicate key error.
#3 Updated by Giovanni Manghi over 5 years ago
Spencer Gardner wrote:
There is no "processing" folder in .qgis2/python/plugins but keep in mind that I'm on Windows
just tested again on Windows (qgis and postgresql/postgis) and the result is always the same for me, if "id" exist in the shapefile a "id_0" column is created in the postgis table.
#5 Updated by Giovanni Manghi over 5 years ago
Spencer Gardner wrote:
The best explanation appears to be that I've got legacy code somewhere causing problems.
if any it would be a "processing" folder inside .qgis2/python/plugins
I'm working in a new tool for processing to import data into postgis, based on ogr2ogr, much faster then any other alternative in QGIS, and with many options available, including the one to choose manually the name of the PK.