Bug report #11550

Import into PostGIS doesn't check for field named "id"

Added by Spencer Gardner over 5 years ago. Updated over 5 years ago.

Status:Closed
Priority:Normal
Assignee:Victor Olaya
Category:Processing/QGIS
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

Description

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.

History

#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.

#4 Updated by Spencer Gardner over 5 years ago

Is there a good way to completely wipe my existing "processing" plugin and start fresh? The best explanation appears to be that I've got legacy code somewhere causing problems.

#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

PS
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.

#6 Updated by Spencer Gardner over 5 years ago

  • Status changed from Feedback to Closed

This appears to be resolved on my machine. Not sure how it was fixed but it must be related to me removing the processing folder. Perhaps it needed a shutdown to clear a cache somewhere or something.

Also available in: Atom PDF