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.