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-14T21: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?

  • 1
    forgot to include scale in the select clause? does that have an effect?
    – Steven Kay
    Jul 15, 2017 at 17:30
  • right, scale was missing! Now I got the error, that there is no primary key defined. How do I solve that?
    – MartinMap
    Jul 15, 2017 at 19:19
  • 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) Jul 17, 2017 at 16:23

1 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.