Writing SQL Query to create spatial view?

Question

I use this sql query to draw a rectangle from a point layer (overview):

create or replace view view_test as
select name, width, height, ST_SetSRID(ST_GeomFromText('POLYGON (('
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||','
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)+(height*(scale*0.1))/2||','
 ||ST_X(geom)+(width * (scale * 0.1))/2||' '||ST_Y(geom)+(height*(scale*0.1))/2||','
 ||ST_X(geom)+(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||','
 ||ST_X(geom)-(width * (scale * 0.1))/2||' '||ST_Y(geom)-(height*(scale*0.1))/2||'))'),32632) as geom
from overview;

But QGIS isn't loading the results:

2017年07月14日T21:38:12 1 dbname='XXXX' host=XX.XX.XXX.XXX port=5432 user='postgres' sslmode=disable key='width' table="public"."view_test" (geom) sql= is an invalid layer - not loaded

Whats wrong with my code?

• Steven Kay: forgot to include scale in the select clause? does that have an effect?
• MartinMap: right, scale was missing! Now I got the error, that there is no primary key defined. How do I solve that?
• Mike -EZUSoft-: If you do not have a unique field in the table, you must define a field with a SQL subquery (SELECT row_number() over () AS uid, * from overview)

Answer 1

From the QGIS wiki:

When you don't have a unique id for each row you want to display, you can create on on the fly using the SQL windows to load your geometries. Simply add in the select : SELECT row_number() OVER( ORDER BY field1,field2 ) AS id_qgis , and select id_qgis as the id column in the QGIS list. It is necessary to specify the Order By clause to ensure the same ID is always assigned to the same row, for every query, regardless of the usage of fields and joins that do affect the row order in the result set.