[GIS] When trying to connect to remote PostGIS database with QGis, getting “ERROR: relation ‘geometry_columns’ does not exist

postgresqlqgis

Getting the error message/popup stating:

Connection failed - Check settings and try again. 

Extended error information: 
ERROR: relation 'geometry_columns' does not exist. 

LINE|1: ...attnum as attnum FROM pg_class c, pg_namespace n, geometry_c... ^

when trying to connect to a PostgreSQL/PostGIS database remotely on QGis (Mac). The pg_hba.conf and postgresql.conf files on the server don't appear to be the issue here. Perhaps there's a way I can grant all privileges for these tables mentioned in the error, but there aren't any tables called 'geometry_columns', so I'm stumped so far.

Version of postgresql on remote server is:

SELECT version();
#=> PostgreSQL 9.1.24 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

Best Answer

I think the answer from Alex Leith is the most likely cause of this error; the geometry_columns table/view gets created as soon as postGIS extension is created.

But it could still happen even if postGIS extension has been added, I think there are two aspects to this..

Table in different schema to postGIS

I found this could happen if the table I was importing was in a different schema to that where the postGIS extension was loaded. This meant that QGIS couldn't "see" the postGIS functions, data types and tables/views, as it assumed it was in the same schema as the database.

Got a very similar warning:-

enter image description here

To reproduce this, I took an existing local postgis-enabled database (with tables in 'public' schema), dropped the postGIS extension, and recreated it in a new schema, 'test')

CREATE EXTENSION postgis
SCHEMA test
VERSION "2.1.2";

Now, attempting to add a table from the 'public' schema will result in that error.

There's a setting called search_path which can be set at database level, which will allow qgis to see schemas other than the one the database is in. If you have access, something like

SHOW search_path;

will typically return something like "$user",public or "$user",postgis. If that list doesn't include the schema postGIS is in, that might be the problem.

Be careful tweaking that setting, I found I got QGIS crashing on opening projects with one database connection I'd altered that way. Luckily, you can remove connections without needing to open a project using the 'elephant' icon. I'll raise a ticket if I ever manage to reproduce this problem.

QGIS expects postGIS to be installed in Public schema

It also looks like qgis expects postgis to be installed in the public schema. If I created a database whose tables and postGIS were both in a schema other than public, I got the message

Database connection was successful, but the accessible tables could not be determined.
Related Question