QGIS - load data by SQL window

Question

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.

Answer 1

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

Answer 2

Check your SQL query statement. The QGIS SQL Query block doesn't like a semicolon at the end of it. Simply get rid of the semicolon (;) if you have so in your query. Under load as new layer, specify your unique value and geometry column, and use Retrieve columns to test these two columns. If all is good, it should work.

Good luck,

Mana