ALTER SCHEMA

Use ALTER SCHEMA to:

Swap the name of a schema with that of another schema.
Rename a schema.
Change owner of a schema.

Syntax

Swap with

To swap the name of a schema with that of another schema:

ALTER SCHEMA <schema1> SWAP WITH <schema2>;

Syntax element	Description
`<schema1>`	The name of the schema you want to swap.
`<schema2>`	The name of the other schema you want to swap with.

Rename schema

To rename a schema:

ALTER SCHEMA <name> RENAME TO <new_name>;

Syntax element	Description
`<name>`	The current name of the schema.
`<new_name>`	The new name of the schema.

Change owner to

To change the owner of a schema:

ALTER SCHEMA <name> OWNER TO <new_owner_role>;

Syntax element	Description
`<name>`	The name of the schema you want to change ownership of.
`<new_owner_role>`	The new owner of the schema.

To change the owner of a schema, you must be the owner of the schema and have membership in the <new_owner_role>. See also Privileges.

Examples

Swap schema names

Swapping two schemas is useful for a blue/green deployment. The following swaps the names of the blue and green schemas.

CREATE SCHEMA blue;
CREATE TABLE blue.numbers (n int);

CREATE SCHEMA green;
CREATE TABLE green.tags (tag text);

ALTER SCHEMA blue SWAP WITH green;

-- The schema which was previously named 'green' is now named 'blue'.
SELECT * FROM blue.tags;

Privileges

The privileges required to execute this statement are:

Ownership of the schema.
In addition,
- To swap with another schema:
  - Ownership of the other schema
- To change owners:
  - Role membership in new_owner.
  - CREATE privileges on the containing database.