1

Configuration:

PostGIS Library: 2.0.3 GEOS: 3.3.8-CAPI-1.7.8 Proj: Rel. 4.8.0, 6 March 2012

PostgreSQL 9.2.2, compiled by VC++ build 1600, 64 bit, running on Win2012 server 64 bit

QGIS 2.2.0 - 32 bit Windows, running on Windows 7 64 bit.

---> The tl;dr

Another way of looking at this issue is that any primary key added to the spatial table causes QGIS to not find a primary key on the table.

<---

I've been struggling with a QGIS/PostGIS related problem for a few days now.

My PostGIS database has a spatial table with integer column called "gid" that is assigned a primary key constraint. That spatial table can be loaded as a spatial layer in QGIS, but opening its table causes QGIS to hang and the table doesn't display. Using the select tool to select one feature within the map display will select all features.

I've also noticed in the layer properties that QGIS is unable to identify a primary key.

I found that if I remove the primary key constraint from the column (now the table does not have any primary key) the spatial table opens fine in QGIS and the issue is no longer apparent, but other issues occur because QGIS cannot identify a primary key for the table.

I've found a work around - if I create a numeric column called oid and populate it with a sequence and do not assign a primary key to the table, QGIS can identify the oid column as a primary key. It doesn't seem to identify a similarly built column name of "gid" as a primary key, however.

I've tried a few different configurations of PostGIS and QGIS.

I've used QGIS 2.0 - 64 bit for Windows and experienced the same issue. I've tried another database server with identical configurations, functions, and extensions, and connecting QGIS to that server and loading spatial layers does not exhibit the issue; QGIS is able to identify a primary key column.

Has anyone experienced issues with QGIS being unable to identify a primary key column when the table has a primary key in Postgres? I've searched through stack overflow and found a few issues that seem similar, but not identical to this problem.

This issue appears similar, but I've used the DB manager and not experienced any success in this situation. When loading a spatial layer from PostGIS via "Add PostGIS Layer" QGIS doesn't provide a drop down box to select a primary key for any spatial layers.

https://hub.qgis.org/issues/9803

  • If I understand: 1. you have a spatial table; 2. the primary is well-formed and have "NOT NULL" option enabled; 3. You have an issue managing the spatial table in QGIS. Am I missing something? – MickyInTheSky May 9 '14 at 11:08
  • Hi @MickyInTheSky that is correct. When the table has a primary key, I'm unable to open and view the table in QGIS. Another odd behavior I noticed is selecting one feature with the selection tool in QGIS will select all features. It looks like QGIS is unable to identify a primary key column when it has a primary key. If I remove the primary key in Postgres, those issues go away, but QGIS has problems with the table at that point because it lacks a primary key. – dfollensbee May 9 '14 at 11:38
  • That is a very strange behaviour. I have experienced similar issue and the problem was in the creation/import of the table in postgis and/or the information in the geometry_columns view. I'd advised to export the table in shp format with shp2pgsql-gui and then import again. – MickyInTheSky May 9 '14 at 12:05
  • Thanks @MickyInTheSky - I gave that a try, but it didn't work. For now, if I view a PostGIS table in QGIS, I've found that eliminating the primary key and enabling OIDs on the table works, but it's a clunky work-around. – dfollensbee May 9 '14 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.