[QGIS-trac] [Quantum GIS] #3820: Layer postgres dont recognize unique key index

[Quantum GIS]

#3820: Layer postgres dont recognize unique key index
------------------------------------------------+---------------------------
   Reporter:  aperi2007                         |              Owner:  nobody       
       Type:  bug                               |             Status:  new          
   Priority:  major: does not work as expected  |          Milestone:  Version 1.7.0
  Component:  Data Provider                     |            Version:  Trunk        
   Keywords:                                    |   Platform_version:  7            
   Platform:  Windows                           |           Must_fix:  No           
Status_info:  0                                 |  
------------------------------------------------+---------------------------
 Hi,

 try-ing to load a view in qgis I'm having this
 error:
 -----
 The view 'gb.v_volumetria_in_aggetto' has no column suitable for use as a
 unique key.
 Quantum GIS requires that the view has a column that can be used as a
 unique key. Such a column should be derived from a table column of type
 int4 and be a primary key, have a unique constraint on it, or be a
 PostgreSQL oid column. To improve performance the column should also be
 indexed.
 The view you selected has the following columns, none of which satisfy the
 above conditions:
 'altezzavolume' derives from 'gb.volumetria_in_aggetto.altezzavolume' and
 is not suitable (type is varchar) and does not have a suitable constraint)
 'codcategoriauso' derives from 'gb.volumetria_in_aggetto.codcategoriauso'
 and is not suitable (type is varchar) and does not have a suitable
 constraint)
 'codvolagg' derives from 'gb.superficie_3d.codvolagg' and is not suitable
 (type is varchar) and does not have a suitable constraint)
 'codvolumetriainaggetto' derives from
 'gb.volumetria_in_aggetto.codvolumetriainaggetto' and is not suitable
 (type is varchar) and has a suitable constraint)
 'documentation' derives from
 'gb.zz_categoriausovolumetriaaggettotype.documentation' and is not
 suitable (type is varchar) and does not have a suitable constraint)
 'geom' derives from 'gb.superficie_3d.geom' and is not suitable (type is
 geometry) and does not have a suitable constraint)
 'id' derives from 'gb.volumetria_in_aggetto.id' and is not suitable (type
 is int4) and does not have a suitable constraint)
 'quotaestrusione' derives from 'gb.volumetria_in_aggetto.quotaestrusione'
 and is not suitable (type is varchar) and does not have a suitable
 constraint)
 'value' derives from 'gb.zz_categoriausovolumetriaaggettotype.value' and
 is not suitable (type is varchar) and has a suitable constraint)
 ----

 I think this is a bug because the id field derives from
 'gg.volumetria_in_aggetto' is really suitable.

 it is a serial (so is unique) and has a unique index created with this
 sql:
 CREATE UNIQUE INDEX IDX_VOLUMINAGG0 ON [schema].VOLUMETRIA_IN_AGGETTO(ID);

 instead qgis refuse it reporting:

 >'id' derives from 'gb.volumetria_in_aggetto.id' and is not suitable (type
 is int4) and does not have a
 >suitable constraint)

 The view is create with this sql:

 CREATE VIEW gb.v_volumetria_in_aggetto AS
 SELECT
         a.id as id,
         a.codvolumetriainaggetto as codvolinag,
         a.altezzavolume as altezvolum,
         a.quotaestrusione as quotaestru,
         c.documentation as codcatuso,
         ST_Union(b.geom) as geom
 FROM
         (( gb.volumetria_in_aggetto a
         LEFT OUTER JOIN [schema].superficie_3d b ON
 (a.codvolumetriainaggetto = b.codvolagg) )
         LEFT OUTER JOIN [schema].zz_categoriausovolumetriaaggettotype c ON
 (a.codcategoriauso = c.value) )
 GROUP BY
         a.id,
         a.codvolumetriainaggetto,
         a.altezzavolume,
         a.quotaestrusione,
         c.documentation
 ;

-- 
Ticket URL: <https://trac.osgeo.org/qgis/ticket/3820>
Quantum GIS <http://qgis.org>
Quantum GIS is an Open Source GIS supporting GDAL/OGR, PostGIS, and GRASS formats, and Web Services