All Products
Search
Document Center

AnalyticDB for PostgreSQL:Manage schemas

Last Updated:Jan 26, 2024

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

DROP SCHEMA myschema CASCADE;