Feature request #1545

Qgis should cope with table aliases in view definitions

Added by Brent Wood over 11 years ago. Updated over 11 years ago.

Status:Closed
Priority:Low
Assignee:nobody -
Category:Data Provider
Pull Request or Patch supplied: Resolution:duplicate
Easy fix?:No Copied to github as #:11605

Description

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 ;
?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."

This error does not appear to be genuine.

History

#1 Updated by gjm - over 11 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.

#2 Updated by Paolo Cavallini over 11 years ago

Apparently a duplicate of #1417

#3 Updated by Paolo Cavallini over 11 years ago

  • Resolution set to duplicate
  • Status changed from Open to Closed

Dupicate of #1417

Also available in: Atom PDF