Why can't I open PostGIS layers in QGIS?

Question

I've set up a table in PostGIS using OSM data (converted using osm2pgsql). I know this database is good, because I'm able to import it and work with it in TileMill, but whenever I try to interact with any of the layers in QGIS (1.8.0, OSX), the Add button is greyed out (even if I put in a query).

I've tested the connection and when I test my queries it shows that lots of rows were returned, so (again), the data's clearly there—I just can't open it up.

CLARIFICATION: It's also worth noting that QGIS is able to see the database just fine. I can build queries and read out data in the Add PostGIS layer interface and everything comes back correctly. The only thing I can't do in the Add PostGIS Layer interface is add the layer.

CLARIFICATION 2 (from the comments): postgis_version returns 2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1. The extensions active on the database are plpgsql (1.0), postgis (2.0.3), and postgis_topology (2.0.3). PostgreSQL and PostGIS were installed using Homebrew.

The table does (now) have a primary key, but QGIS doesn't appear to find it.

Answer 1

The problem, as it turns out, was that I had not named the connection.

Answer 2

From Tim Sutton's Blog:

...QGIS’ requirement that the view have a column derived from a primary key or a column with a unique constraint on it, you can cast the row number to int4 like this:

(Emphasis mine)

I believe that the specific int4 requirement is no longer true, but ensure that your field is either explicitly a primary key, or is unique.

Alternatively, you could use Mr. Sutton's query to create a view with the PostGre "ROW_NUMBER()" function, reprinted here (also from Sutton's blog):

create view vw_farms
as
select int4(ROW_NUMBER() over (order by parcel.farm_name)) as id,
parcel.farm_name,
parcel.geometry
from parcel
where parcel.farm_name != '' limit 10 ;

Answer 3

Have the tables a definite type (point,layer,polygon)?

If you have not checked "use estimated table data" when establishing a new connection, Postgis searches across the whole tables to get the geometry types every time you want to connect to the database. This will take a lot of time when using larger OSM datasets.

---

EDIT

This is how my Add postgis layer dialogue looks like:

So no Primary key necessary, but two indices (not shown here). Do you have an osm2pgsql version that is compiled for postgis 2.0? Mine is for postgis 1.5 (on Windows), and it works since years. With Postgis 2.0, there have been many changes in commands.