Set deafult schema while querying in pgAdmin 4 with query tool

Question

Whenever i have to execute sql commands in pgAdmin 4 i have to append schema_name with tables. Eg :-

SELECT * FROM my_schema.users //where myschema is schema name
ORDER BY id ASC  

Is there any way to execute sql commands in pgAdmin 4 without schema name, by setting a default schema in advance.

Some thing like this

SELECT * FROM programme
ORDER BY id ASC 

- without specifying schema name with table.

Is there a way to set default schema in pgAdmin 4 for querying in sql tool?

Answer 1

You can do it in 2 ways:

SET search_path = my_schema, "$user", public; -- For current session only

ALTER ROLE your_role SET search_path = my_schema, "$user", public; -- Persistent, for role

You can also set it for whole database, same way as for role.

EDIT: Just to explain what this does - it will change where and in what order Postgres will search for objects matching object identifiers that did not get prefixed with schema name.