Schemas

Object names can be reused in different schemas without conflict. The schema contains all the database-named objects, including tables, views, functions, aggregates, indexes, sequences, triggers, data types, domains, and ranges:

By default, there is a schema called public in the template databases. That means all the newly-created databases also contain this schema. All users, by default, can access this schema implicitly. Again, this is inherited from the template databases. Allowing this access pattern simulates a situation where the server is not schema-aware. This is useful in small companies where there's no need to have complex security. Also, this enables a smooth transition from non-schema-aware databases.

When a user wants to access a certain object, they need to specify the schema name and the object name separated by a period (.). If the search_path database setting doesn't contain this name, or if the developer likes to use fully-qualified names (for example, to select all the entries in pg_database in the pg_catalog schema), you need to write the following command:

SELECT * FROM pg_catalog.pg_database;
--Alternatively you can also use the following command:
TABLE pg_catalog.pg_database;

Qualified database-object names are sometimes tedious to write, so most developers prefer to use the unqualified object name, which is composed of the object name without the schema. PostgreSQL provides a search_path setting that's similar to the using directive in the C++ language. search_path is composed of schemas that are used by the server to search for the object. The default search_path, as shown in the following code, is $user, public. If there's a schema with the same name as the user, it will be used first to search for objects or to create new objects. If the object isn't found in the schemas specified in the search_path, an error will be thrown as follows:

postgres=# SHOW search_path;
   search_path 
-----------------
 "$user", public
(1 row)