PostgreSQL ALTER SCHEMA Statement

Summary: In this tutorial, you’ll learn how to modify a schema using the PostgreSQL ALTER SCHEMA statement.

The ALTER SCHEMA statement allows you to change the definition of a schema, including renaming a role and changing the owner to another.

Renaming a schema #

To rename a schema, you use the following form of the ALTER SCHEMA statement:

ALTER SCHEMA schema_name TO new_name;

In this syntax:

First, specify the schema name you want to change after the ALTER SCHEMA keywords.
Second, provide the new name after the TO keyword.

The role must own the schema to run the ALTER SCHEMA statement. Additionally, it must have the CREATE privilege for the database. For example:

First, create a new schema called hr:

CREATE SCHEMA hr;

Second, rename the schema hr to hcm:

ALTER SCHEMA hr TO hcm;

To change the owner of the schema, you use the following ALTER SCHEMA statement:

ALTER SCHEMA schema_name
OWNER TO new_owner;

The new_owner can be any role or CURRENT_ROLE, CURRENT_USER, SESSION_USER.

The new owner must have CREATE privilege for the database.

The role that executes this statement must be able to SET ROLE to the new owner.

For example:

First, create a new role with LOGIN privilege:

CREATE ROLE joe
WITH LOGIN PASSWORD 'SecurePassword';Code language: JavaScript (javascript)

Second, create a new schema with the owner as joe:

CREATE SCHEMA marketing;

Third, change the owner of the marketing schema to joe:

ALTER SCHEMA marketing
OWNER TO joe;

Was this tutorial helpful ?