How to run (SQL QUERY) in a different schema in QGIS DB Manager?

Question

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

I was successful...

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

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

Answer 1

Schema and Table need to be separately quoted.

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

Answer 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.