[GIS] QGIS – load data by SQL window

postgisqgis

I have problem with QGIS (2.0.1), PostGIS (2.0.4) combination.
– A can load tables or views by Add PostGIS layer. This is OK.
– But a can't load data by DB Manager and SQL Window. Executing query run but load result is problem.

Error message is:

PostgreSQL layer has no primary key

But Primary key exist in table. DB Manager find out them itself.

Do you have any idea where is problem?
Could my problem be with different schema then public?

Edit: Do this when I click to Load Now only. And realy do this outside public schema. In public schema it's OK.

Best Answer

QGIS needs more than a primary key, that primary key field has to be an integer. What I do if I have a query or table that doesn't have a unique interger field I'll fake one in my query with the row_number window function.

SELECT row_number() OVER() As fake_id, geom FROM blahblahblah

Then in the Load as New Layer screen I click Retrieve columns, then set the primary key drop down to my new fake_id field.

For more information on window functions check out Postgres' manual: http://www.postgresql.org/docs/9.3/static/tutorial-window.html

Related Question