1

I would like to load a PostgreSQL table without geometry and with a composite primary key in QGIS (there is no unique column, see below).
This yields “ERROR” in all cells. It is not resolved by adding a SERIAL column.

The same occurs when loading using DB Manager. But the issue is resolved when I “Edit table” in DB Manager, using “SELECT * from table” and “Load as new layer”. If I select all records, DB Manager adds a unique _uid_ column. If I select one subgroup from (id_name, sub_id), I can select the time_ column as unique values which also works.

What am I missing for QGIS to load the table directly from PostgreSQL? Is a unique column as primary key required, or should I look elsewhere?

I am using a PostgreSQL/PostGIS database v9.4.6 and QGIS 2.14.9 ltr.

Table info:
Fields:
Name - Type - Length - Null
id_name - varchar - (30) - N
sub_id - varchar - (30) - N
time_ - timestamp - 8 - N
mtaw - float8 - 8 - Y
mmv - float8 - 8 - Y

Constraints:
Name - Type - Column(s)
timeseries_values_pkey - Primary key - id_name, sub_id, time_
timeseries_values_id_name_fkey - Foreign key - id_name, sub_id

  • Could you share a small sample of data to test? Do you know what the PostGIS log is showing in QGIS (access from bottom right speech bubble)? – raphael Jan 13 '17 at 14:48
  • 1
    I would think the unique ID field that QGIS requires needs to be of INTEGER type... even a dummy OBJECTID field of SERIAL type would work. Your primary keys are built on CHARACTER fields, which won't serve as unique ID's for QGIS to work with. – DPSSpatial Jan 13 '17 at 15:25
  • @raphael, thanks for the suggestion. The PostGIS log shows nothing if I submit the original table. However, after removing the keys I can view the data in QGIS and get the message below. Also, if I limit the number of records: no problem. I have to look at the data I guess. Thanks. Message= 'Primary key is ctid - changing of existing features disabled (; "public"."test_full").' – haestp Jan 13 '17 at 16:00
  • @DPSSpatial, I understand your point about the required unique identifier. But I think the problem here is in the data and/or the primary key. QGIS is able to show the values if no primary key is defined, or if I subset the first 100 values (one id_name). – haestp Jan 13 '17 at 20:41
  • @haestp try creating an objectid field of serial integer type and set it to primary key - just to test editing abilities... – DPSSpatial Jan 14 '17 at 14:06

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.