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;