PostgreSQL | Schema | Modify a Schema (ALTER SCHEMA)

Tags:

• PostgreSQL
• Database

This page explains how to modify a created schema by using the ALTER SCHEMA command. The schema name and owner can be changed.

Rename a Schema

First, here is how to rename a schema by using the ALTER SCHEMA command. The syntax is as follows.

ALTER SCHEMA name RENAME TO new_name

This changes the current schema name (name) to the new schema name (new_name).

To rename a schema, the role executing the command must be a superuser or the schema owner, and it must have the CREATE privilege in the database where the schema is created.

–

Now try it in practice. If you run the psql meta-command \dn, you can see that the current devkuma database has two schemas in addition to the default public schema.

devkuma=# \dn
   스키마(schema) 목록
     이름      |  소유주
---------------+----------
 myschema      | postgres
 orange_schema | orange
 public        | postgres
(3개 행)


devkuma=#

Rename the orange_schema schema to fruit. Run the following.

devkuma=# alter schema orange_schema rename to fruit;
ALTER SCHEMA
devkuma=#

The schema name has been changed. To confirm, run the \dn command again.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 fruit    | orange
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=#

You can confirm that the orange_schema schema has been renamed to fruit.

Change the Schema Owner

Next, here is how to change the owner of a schema by using the ALTER SCHEMA command. The syntax is as follows.

ALTER SCHEMA name OWNER TO new_owner

This changes the owner of the schema (name) to the new owner (new_owner).

To change the owner of a schema, the role executing the command must be a superuser or the schema owner, be a direct or indirect member of the new owner role, and have the CREATE privilege in the database where the schema is created.

–

Now try it in practice. If you run the psql meta-command \dn, you can see that the current devkuma database has two schemas in addition to the default public schema.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 fruit    | orange
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=#

Change the owner of the fruit schema to the kimkc role. Run the following.

devkuma=# alter schema fruit owner to kimkc;
오류:  "kimkc" 롤(role) 없음
devkuma=#

If the role does not exist as shown above, an error occurs. Create a role as follows.

devkuma=# create role kimkc with superuser login password '1234';
CREATE ROLE
devkuma=#

Now run the command again.

devkuma=# alter schema fruit owner to kimkc;
ALTER SCHEMA
devkuma=#

The schema owner has been changed. To confirm, run the \dn command again.

devkuma=# \dn
 스키마(schema) 목록
   이름   |  소유주
----------+----------
 fruit    | kimkc
 myschema | postgres
 public   | postgres
(3개 행)


devkuma=#

You can confirm that the owner of the fruit schema has changed to the kimkc role.

–

This page explained how to modify a created schema by using the ALTER SCHEMA command.