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.

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;
Note 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:


For more information, visit Pivotal Greenplum documentation.