Schema

Object names can be reused in different schema 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 the situation where the server is not schema-aware. This is useful in small companies where there is no need to have complex security. Also, this enables smooth transition from non-schema-aware databases.

In a multiuser and multidatabase environment setup, remember to revoke the ability for all users to create objects in the public schema. This is done by the following command in the newly created database, or in the template1 database: REVOKE CREATE ON SCHEMA public FROM PUBLIC;

When a user wants to access a certain object, he needs to specify the schema name and the object name separated by a period . If the search_path database setting does not 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), one needs 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 many 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 is similar to the using directive in the C++ language. The 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 is a schema with the same name as the user, then it will be used first to search for objects or to create new objects. If the object is not found in the schemas specified in the search_path, then an error will be thrown:

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