Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Improper cast of textual primary key during import with DB Manager #55856

Closed
1 of 2 tasks
alhyss opened this issue Jan 16, 2024 · 0 comments · Fixed by #56243
Closed
1 of 2 tasks

Improper cast of textual primary key during import with DB Manager #55856

alhyss opened this issue Jan 16, 2024 · 0 comments · Fixed by #56243
Labels
Bug Either a bug report, or a bug fix. Let's hope for the latter! DB Manager Relating to the DB Manager core plugin PostGIS data provider

Comments

@alhyss
Copy link

alhyss commented Jan 16, 2024 •
edited
Loading

What is the bug or the crash?

I want to import a layer into a PostGIS database using DB Manager, with a custom field as primary key and the "Convert field names to lowercase" option. In QGIS, the field in question is typed as text. Though most values are integers, two records have non-numerical values '2A' and '2B'.

While importing the layer, DB Manager apparently tries to cast the field as integer (serial), which fails with an "invalid syntax for type integer" error.

Importing the layer with the custom primary key works if I don't convert the field names to lowercase. Importing the layer with lowercase field names works if I don't declare the field as primary key.

Steps to reproduce the issue

  1. Download the official list of French departments.

  2. Load the file into QGIS with Layer > Add Layer > Add Delimited Text Layer (encoding UTF-8, format CSV, no geometry).

  3. Open DB Manager.

  4. Click on Import Layer/File and import the layer into a PostGIS database with the following options:

    image

Versions

PostgreSQL 13.7

QGIS :

Version
Core
QGIS version 3.34.2-Prizren
QGIS code revision 7d19979
Qt version 5.15.3
Python version 3.9.5
GDAL/OGR version 3.8.2
PROJ version 9.3.1
EPSG Registry database version v10.098 (2023-11-24)
GEOS version 3.12.1-CAPI-1.18.1
SQLite version 3.41.1
PDAL version 2.6.0
PostgreSQL client version 15.2
SpatiaLite version 5.1.0
QWT version 6.1.6
QScintilla2 version 2.13.4
OS version Windows 10 Version 2009
Active Python plugins
asgardmanager 1.3.4
AsgardMenu 1.8
plume 1.1.0
db_manager 0.1.20
MetaSearch 0.3.6
processing 2.12.99

Supported QGIS version

  • I'm running a supported QGIS version according to the roadmap.

New profile

Additional context

No response

EDIT: with QGIS 3.34.2.
@alhyss alhyss added the Bug Either a bug report, or a bug fix. Let's hope for the latter! label Jan 16, 2024
@agiudiceandrea agiudiceandrea added DB Manager Relating to the DB Manager core plugin PostGIS data provider labels Jan 16, 2024
ptitjano added a commit to ptitjano/QGIS that referenced this issue Feb 7, 2024
@ptitjano
qgspostgresprovider: Fix primary key retrieval with lowercase option
ba315b4 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issues is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: qgis#55856
@ptitjano ptitjano mentioned this issue Feb 7, 2024
Merged
ptitjano added a commit to ptitjano/QGIS that referenced this issue Feb 7, 2024
@ptitjano
qgspostgresprovider: Fix primary key retrieval with lowercase option
5292f0b 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issues is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: qgis#55856
ptitjano added a commit to ptitjano/QGIS that referenced this issue Feb 7, 2024
@ptitjano
qgspostgresprovider: Fix primary key retrieval with lowercase option
44592ef 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issues is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: qgis#55856
ptitjano added a commit to ptitjano/QGIS that referenced this issue Feb 7, 2024
@ptitjano
qgspostgresprovider: Fix primary key retrieval with lowercase option
dc3d145 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issues is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: qgis#55856
ptitjano added a commit to ptitjano/QGIS that referenced this issue Feb 7, 2024
@ptitjano
qgspostgresprovider: Fix primary key retrieval with lowercase option
c6ae6d5 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issues is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: qgis#55856
nyalldawson pushed a commit that referenced this issue Feb 8, 2024
@ptitjano @nyalldawson
qgspostgresprovider: Fix primary key retrieval with lowercase option
f090bdf 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issues is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: #55856
ptitjano added a commit to ptitjano/QGIS that referenced this issue Feb 8, 2024
@ptitjano
qgspostgresprovider: Fix primary key retrieval with lowercase option
a1e4205 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issue is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: qgis#55856
@ptitjano ptitjano mentioned this issue Feb 8, 2024
Merged
nyalldawson pushed a commit that referenced this issue Feb 8, 2024
@ptitjano @nyalldawson
qgspostgresprovider: Fix primary key retrieval with lowercase option
1a8f897 
The `lowercaseFieldNames` allows to transform the column names to
lowercase. If this option is enabled, then the requested primary
key (`primaryKey`) is in lowercase but the `fields` are
not. Therefore, the requested primary key will never be found.

This issue is fixed by converting the field names to lowercase when
looking for the primary key if the option is enabled.

Closes: #55856
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug Either a bug report, or a bug fix. Let's hope for the latter! DB Manager Relating to the DB Manager core plugin PostGIS data provider
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

qgspostgresprovider: Fix primary key retrieval with lowercase option ptitjano/QGIS
2 participants
@agiudiceandrea @alhyss