Bug report #3820

Layer postgres dont recognize unique key index

Added by aperi2007 - over 8 years ago. Updated over 7 years ago.

Status:Closed
Priority:Low
Assignee:-
Category:Data Provider/PostGIS
Affected QGIS version:master Regression?:No
Operating System:Windows Easy fix?:No
Pull Request or Patch supplied:No Resolution:worksforme
Crashes QGIS or corrupts data:No Copied to github as #:13878

Description

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
;

History

#1 Updated by aperi2007 - over 8 years ago

oops,
the query used is this:

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 gb.superficie_3d b ON (a.codvolumetriainaggetto = b.codvolagg) ) LEFT OUTER JOIN gb.zz_categoriausovolumetriaaggettotype c ON (a.codcategoriauso = c.value) )
GROUP BY
a.id, a.codvolumetriainaggetto, a.altezzavolume, a.quotaestrusione, c.documentation
;

#2 Updated by aperi2007 - over 8 years ago

I do some other test.
I notice it work if the 'id' field is a primary key.
So the existence of an unique index on a int4 field seem to be not enough.
But it must be also primary key.

I don't know if this is a bug or not.

#3 Updated by Giovanni Manghi over 8 years ago

duplicate of this (old) ticket? #62

#4 Updated by aperi2007 - over 8 years ago

I guess no.

The #62 ask for a field not only int4 (for example varchar)
but always with an unique index on it.

This ticket report that the field (int4 type) must be PrimartKey and not only with
an unique index.

#5 Updated by Giovanni Manghi almost 8 years ago

  • Target version changed from Version 1.7.0 to Version 1.7.4

#6 Updated by Jürgen Fischer over 7 years ago

  • Category changed from Data Provider to Data Provider/PostGIS
  • Status changed from Open to Feedback
  • Crashes QGIS or corrupts data set to No
  • Affected QGIS version set to master
  • Pull Request or Patch supplied set to No
  • Assignee deleted (nobody -)

please verify that master is still affected.

#7 Updated by Paolo Cavallini over 7 years ago

  • Target version changed from Version 1.7.4 to Version 1.8.0

#8 Updated by Jürgen Fischer over 7 years ago

  • Resolution set to worksforme
  • Status changed from Feedback to Closed

closing for the lack for feed back - the postgres provider requires to select the pk of view now.

Also available in: Atom PDF