Feature request #1545
Qgis should cope with table aliases in view definitions
|Pull Request or Patch supplied:||Resolution:||duplicate|
|Easy fix?:||No||Copied to github as #:||11605|
A view with a unique primary key can't be opened by QGIS as it says the key is not unique. It is.
This is on Qgis v0.11, but also I believe in v1.0.
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 ;
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;
ofop=# select count() from (select id from obs group by id having count() > 1 limit 1) as foo;
But QGIS gives an error msg:
"Note: 'id' initially appeared suitable but does not contain unique data, so is not suitable."
This error does not appear to be genuine.
#1 Updated by gjm - over 12 years ago
After some debugging with Brent (aka pcreso), we found the problem was that qgis is unable to deal with columns in a view where the view definition contained table aliases and the view column was renamed using AS. The symptoms weren't a good indication of the underlying problem.
For example, in this view:
qgis won't see the id column. If the view was: <pre> qgis would work fine. This is a deficiency with how qgis parses the view definitions. So, to turn this ticket around, I've changed it to an enhancement that qgis parse table aliases. Adding in the patch in #1535 would also provide for a way around qgis not correctly parsing other details in view definitions.