5

My last question was How to QUERY (SQL) in QGIS DB Manager? The result was this:

enter image description here

I was successful...

But when I try to use the same layer in another SCHEMA (Not in Public) it gives me this error:

enter image description here

Could somebody tell me: what i´m doing wrong here?

6

Schema and Table need to be separately quoted.

SELECT * FROM "BD_Maputo"."Capitais_Provinciais" ...
2

You can also add additional schema to your search path so you don't need to specify the schema name each time you want to run a query. If you have admin rights you can alter the search path for the entire database with:

ALTER DATABASE <database_name> SET search_path TO schema1,schema2;

or if you don't have admin rights you can change the search path just for your role:

ALTER ROLE <role_name> SET search_path TO schema1,schema2;

In your case the command would look something like:

ALTER DATABASE <database_name> SET search_path TO public, "BD_Maputo"
  , topology, tiger, tiger_data;

Just replace with the actual name of your database. Don't forget to include public in your new search_path, otherwise it may cause problems when you try to reference PostGIS functions.

Also, if you don't want to use double quotes in your queries you can alter your schema, table, and column names to use all lower case.

For more information on search_path see the Postgres Manual.

Edit: If you do alter your search path you have to establish a new connection to be able to use the new search path. In your case, closing and re-opening QGIS will work.

  • +1 for the search_path approach, although I would highly discourage altering system variables without adequate experience and understanding, especially in a production database (even on the database level). – Gabor Farkas Mar 1 '17 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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