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
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.
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;
ALTER ROLEstatement 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
current_schema()function to view the current schema. Example:
SHOWstatement to view the current search path. Example:
Delete a schema
DROP SCHEMAstatement to delete a schema. Example:
DROP SCHEMA myschema;
DROP SCHEMA myschema CASCADE;
For more information, visit Pivotal Greenplum documentation.