Layer postgres dont recognize unique key index

[aperi2007]

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

;

[aperi2007]

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

;

[aperi2007]

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.

[lutra]

duplicate of this (old) ticket? #62

[aperi2007]

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.