Manage schemas
A schema is a named namespace within a database that groups related objects: tables, indexes, views, stored procedures, and operators. Each schema is unique to its database. Each database starts with a default schema named public, and all roles have CREATE and USAGE permissions on it by default. If no schemas are created, objects are created in the public schema.
Use schemas to:
Let multiple teams work in the same database without name collisions.
Organize objects into logical groups for easier management.
Isolate third-party or application objects from your own.
Create a schema
Run CREATE SCHEMA to create a schema:
CREATE SCHEMA <schema_name> [AUTHORIZATION <username>]| Parameter | Description |
|---|---|
<schema_name> | Name of the schema. |
<username> | Role that owns the schema. If omitted, the role running the statement becomes the owner. |
Example:
CREATE SCHEMA myschema;Set the search path
The search_path parameter controls the order in which schemas are searched when an object name is not schema-qualified. Set it at the database level or for a specific role.
Database level:
ALTER DATABASE mydatabase SET search_path TO myschema, public, pg_catalog;Role level:
ALTER ROLE sally SET search_path TO myschema, public, pg_catalog;View schemas
View the active schema:
SELECT current_schema();View the current search path:
SHOW search_path;Delete a schema
Run DROP SCHEMA to delete a schema. By default, the schema must be empty.
DROP SCHEMA myschema;To delete a schema along with all its objects (tables, data, functions, and more), use CASCADE:
DROP SCHEMA myschema CASCADE;