[Qgis-user] Constraints on postgresql views

[Yves Jacolin]

Le vendredi 02 avril 2010 10:16:40, Lionel Roubeyrie a écrit :
> Hi all,
> I try to construct views from a lines table to group roads' portions
> together, but I fall on this view when called from QGIS :
> SELECT 1::oid AS oid, ST_Multi(ST_Union(f.the_geom)) as the_geom
> FROM (SELECT (ST_Dump(the_geom)).geom As the_geom FROM v_tmp) As f
>
> but qgis says :
> The view 'public.v_tmp2' has no column suitable for use as a unique key.
> Qgis 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:
> 'the_geom' derives from 'public.tmp.the_geom' and is not suitable
> (type is geometry) and does not have a suitable constraint)
>
> thanks if someone can explain me why this doesn't work
Lionel,

Just add an int4 column like this :

SELECT  f.id,  AS oid, ST_Multi(ST_Union(f.the_geom)) as the_geom FROM (SELECT 
id,_Dump(the_geom)).geom As the_geom FROM v_tmp) As f

id column in v_tmp must exists. You can add one in v_temp if v_temp is not a 
view:
ALTER TABLE v_temp ADD COLUMN id (serial);

Regards,

Y.
-- 
Yves Jacolin

http://yjacolin.gloobe.org