Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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). Postgres and postgis were installed using Homebrew.

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


share|improve this question
Please elaborate more. How do you try to add the content of the database table? Using the Add PostGIS Layers button? –  Niculita Mihai Jul 29 '13 at 7:14
Yes, using the Add PostGIS Layers option. –  futuraprime Jul 29 '13 at 12:58
Have you tried the solutions from gis.stackexchange.com/questions/38937/… already? –  Andre Joost Jul 29 '13 at 15:15
Yes. 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). FWIW, postgres and postgis were installed using Homebrew. –  futuraprime Jul 29 '13 at 17:56
Does your table have a unique primary key field, and are you making sure QGIS is looking at that field for the PK? See: gis.stackexchange.com/questions/25699/… –  DPierce Jul 29 '13 at 18:19
show 2 more comments

3 Answers

up vote 2 down vote accepted

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

share|improve this answer
add comment

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
select int4(ROW_NUMBER() over (order by parcel.farm_name)) as id,
from parcel
where parcel.farm_name != '' limit 10 ;
share|improve this answer
add comment

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.


This is how my Add postgis layer dialogue looks like:

enter image description here

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.

share|improve this answer
They do have definite types (two layers with lines, one with points, and one with polygons). I did not have "use estimated table data" checked, but I've tried it now with that checked, and the add button remains greyed out. I haven't tried just waiting--I'll give that a go. –  futuraprime Jul 29 '13 at 14:54
Unfortunately, half an hour later, it still hasn't enabled "Add". It's not an especially big table (~36k rows)... seems like that should've parsed by now. –  futuraprime Jul 29 '13 at 15:33
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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