Reference: Schemas

A schema is a namespace inside a database. Tables, views, sequences, functions, and types always belong to exactly one schema. Objects in different schemas can share a name without clashing.

CREATE SCHEMA inventory;
CREATE SCHEMA sales;

CREATE TABLE inventory.trees (id integer, species text);
CREATE TABLE sales.trees (id integer, price numeric);

Every new database contains the schema public, where objects end up if you don’t specify a schema. The system schemas pg_catalog and information_schema contain the system tables.

Using Schemas

You can always refer to an object by its schema-qualified name, such as inventory.trees. For unqualified names, CedarDB searches the schemas listed in the search_path setting, in order. It creates new unqualified objects in the first schema of the list that exists.

SET search_path = inventory, public;

SELECT * FROM trees;             -- reads inventory.trees
CREATE TABLE saplings (id int);  -- creates inventory.saplings

The default search_path is "$user", public. "$user" stands for a schema with the same name as the current role, if it exists. current_schema() returns the schema CedarDB creates new objects in, and current_schemas(true) returns the effective search path including pg_catalog.

If none of the schemas in the search path exists, creating an unqualified object fails.

Each session that creates temporary objects gets its own temporary schema pg_temp_<n>, which CedarDB searches before the schemas in search_path. The name pg_temp always refers to the temporary schema of the current session (see Temporary tables).

CREATE SCHEMA

CREATE SCHEMA creates a new, empty schema in the current database.

CREATE SCHEMA [ IF NOT EXISTS ] <name>
    [ AUTHORIZATION { <role_name> | CURRENT_USER | CURRENT_ROLE | SESSION_USER } ]

Create a schema that belongs to another role:

CREATE ROLE forester LOGIN;
CREATE SCHEMA forestry AUTHORIZATION forester;

SELECT nspname, nspowner::regrole FROM pg_namespace WHERE nspname = 'forestry';

 nspname  | nspowner
----------+----------
 forestry | forester

Permissions

To create a schema, you need the CREATE privilege on the current database. The owner of a database has this privilege by default. With AUTHORIZATION, you must also be able to SET ROLE to the new owner, i.e., be a member of that role (see Roles). Superusers can create any schema.

ALTER SCHEMA

ALTER SCHEMA renames a schema or changes its owner.

ALTER SCHEMA <name> RENAME TO <new_name>
ALTER SCHEMA <name> OWNER TO { <new_owner> | CURRENT_USER | CURRENT_ROLE | SESSION_USER }

CREATE SCHEMA inventory;
ALTER SCHEMA inventory RENAME TO stock;

Objects inside the schema move with it. Queries that use the old name stop working. As with CREATE SCHEMA, the new name must not start with pg_.

CREATE ROLE forester LOGIN;
ALTER SCHEMA stock OWNER TO forester;

Permissions

To alter a schema, you must own it or be a superuser. Members of the owning role count as owners. In addition:

• RENAME TO requires the CREATE privilege on the current database.
• OWNER TO requires that you can SET ROLE to the new owner, and that the new owner has the CREATE privilege on the current database.

DROP SCHEMA

DROP SCHEMA removes a schema.

DROP SCHEMA [ IF EXISTS ] <name> [, ...] [ CASCADE | RESTRICT ]

CREATE SCHEMA inventory;
DROP SCHEMA inventory;

By default (RESTRICT), you can only drop an empty schema. Use CASCADE to also drop all objects in the schema:

CREATE SCHEMA inventory;
CREATE TABLE inventory.trees (id integer, species text);

DROP SCHEMA inventory;
-- ERROR:  cannot drop schema inventory because other objects depend on it
-- DETAIL:  table trees depends on it

DROP SCHEMA inventory CASCADE;

Use IF EXISTS to avoid an error if the schema does not exist.

Permissions

To drop a schema, you must own it, be a member of the owning role, or be a superuser. The owner of a schema can drop it with CASCADE, even if it contains objects owned by other roles. The owner of a schema can also drop any object in it.

Schema privileges

Schema privileges control who can use and create objects in a schema.

The owner of a schema has all privileges on it. Other roles have no privileges on a new schema.

The public schema is owned by the built-in role pg_database_owner, which always stands for the owner of the current database. By default, every role has USAGE on public, but only the database owner can create objects in it.

SELECT nspacl FROM pg_namespace WHERE nspname = 'public';

                            nspacl
---------------------------------------------------------------
 {=U/pg_database_owner,pg_database_owner=UC/pg_database_owner}

Let a role read tables in a schema. The role needs both USAGE on the schema and SELECT on the table:

CREATE SCHEMA inventory;
CREATE TABLE inventory.trees (id integer, species text);
CREATE ROLE forester LOGIN;

GRANT USAGE ON SCHEMA inventory TO forester;
GRANT SELECT ON inventory.trees TO forester;

Without USAGE, queries fail with permission denied for schema 'inventory', even if the role has SELECT on the table.

For the full GRANT and REVOKE syntax, see Roles.

PostgreSQL Differences

• CREATE SCHEMA does not support schema elements, such as CREATE SCHEMA s CREATE TABLE .... Create the objects with separate statements.
• CREATE SCHEMA AUTHORIZATION <role> without a schema name is not supported. Specify the schema name.
• COMMENT ON SCHEMA is not supported.