PostgreSQL: Modifying Schemas with ALTER SCHEMA

Use PostgreSQL's `ALTER SCHEMA` command to modify existing schemas. This guide covers renaming schemas and changing ownership.

Using PostgreSQL's `ALTER SCHEMA` Command

In PostgreSQL, the `ALTER SCHEMA` command modifies the definition of an existing schema. Schemas are containers for database objects (tables, functions, etc.), helping organize your database. This guide explains how to rename a schema and change its owner using `ALTER SCHEMA`.

Renaming a Schema

To rename a schema, use the `RENAME TO` clause:

ALTER SCHEMA schema_name RENAME TO new_schema_name;

Replace `schema_name` with the existing schema's name and `new_schema_name` with the desired new name. The new name cannot start with `pg_` (reserved for system schemas). You must have ownership of the schema and CREATE privileges on the database to execute this command.

Examples of Renaming Schemas

(Note: The original content includes screenshots showing the `psql` output after renaming schemas. Since images cannot be directly displayed here, please refer to the original document for visual confirmation of the commands' success. The descriptions below aim to convey the information in those screenshots.)

• Renaming `myschema` to `Schema1`:
• Renaming `Company` to `department`:

Changing a Schema's Owner

To change a schema's owner, use the `OWNER TO` clause:

ALTER SCHEMA schema_name OWNER TO new_owner;

Replace `schema_name` with the schema's name and `new_owner` with the new owner's name (or `CURRENT_USER` for the current user, or `SESSION_USER` for the session user). You need appropriate privileges to change ownership.

Example: Changing Schema Ownership

This example changes the owner of `Schema1` to `postgres`:

ALTER SCHEMA Schema1 OWNER TO postgres;

The command to list user-created schemas (excluding system schemas):

SELECT * FROM pg_catalog.pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspacl IS NULL ORDER BY nspname;

(Note: The original text includes a screenshot showing the output of this query, confirming the ownership change. Since images cannot be included here, please refer to the original document for visual verification. The description below aims to convey the information in that screenshot.)

The output shows a list of schemas; you'll see that the owner ID for the renamed schema now reflects the `postgres` user.