[Qgis-user] Possible Postgis view opening bug

[pcreso at pcreso.com]

Hi,

I'm trying to open a PostGIS view in QGIS, & get told there is no suitable key. I don't want to file a bug if it is my mistake, so figured I'd ask if anyone else has a similar problem or can point out where I've gone wrong.

However, I believe there is a suitable key, details below:



SELECT DISTINCT o.id AS obs_id, 
                o.trip_code, 
                o.station_no, 
                o."timestamp", 
                o.obs_id AS obs_type_id,
                t.category, 
                t.descr, 
                o.version, 
                o."comment", 
                s.speed, 
                s.course, 
                s.heading, 
                s.depth, 
                s.main_substrate, 
                s.geom
FROM obs o,
     obs_type t,
     spline s
WHERE o.obs_id = t.obs_id
  AND o."timestamp" = s."timestamp";


The o.id (obs_id) column is a primary key in obs, so is guaranteed unique in the underlying table. S.timestamp has a unique index on it in the underlying table, as does obs_type.id.

I believe QGIS uses the following SQL to test for uniqeness in a column, which returns true in this case:
select count(distinct obs_id) = count(obs_id) from v_obs_pos ;
 ?column?
----------
 t
(1 row)


My version of the same test gives 0 duplicates in the underlying table & the view:
select count(*) from (select obs_id from v_obs_pos group by obs_id having count(*) > 1 limit 1) as foo;
 count
-------
     0
(1 row)
ofop=# select count(*) from (select id from obs group by id having count(*) > 1 limit 1) as foo;
 count
-------
     0
(1 row)



But QGIS gives an error msg: 
"Note: 'id' initially appeared suitable but does not contain unique data, so is not suitable."



Does anyone have any ideas?


Thanks,

  Brent Wood