A schema is the namespace of a database. It is a set of objects in a database. These objects include tables, indexes, views, stored procedures, and operators. A schema is unique to each database. Each database has a default schema named public.
If no schemas are created, objects are created in the public schema. All database roles (users) have CREATE and USAGE permissions in the public schema.
Create a schema
Execute the CREATE SCHEMA statement to create a schema. The syntax is as follows:
CREATE SCHEMA <schema_name> [AUTHORIZATION <username>]<schema_name>: the name of the schema.
<username>: the name of the role that owns the schema. If this parameter is not specified, the role that executes the statement owns the schema.
Example:
CREATE SCHEMA myschema;Set a path to search for schemas
The search_path parameter specifies the order in which schemas are searched for.
You can use the ALTER DATABASE statement to set a search path. Example:
ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;You can also use the ALTER ROLE statement to set a search path for a specific role (user). Example:
ALTER ROLE sally SET search_path TO myschema, public, pg_catalog;View the current schema
Execute the current_schema() function to view the current schema. Example:
SELECT current_schema();Execute the SHOW statement to view the current search path. Example:
SHOW search_path;Delete a schema
Execute the DROP SCHEMA statement to delete a schema. Example:
DROP SCHEMA myschema;By default, you can only delete a schema if it is empty.
To delete a schema and all objects (such as tables, data, and functions) in it, execute the following statement:
DROP SCHEMA myschema CASCADE;