PostgreSQL Schema

This article discusses the basic usage of PostgreSQL schemas, including creating schemas, modifying schemas, and dropping schemas.

PostgreSQL allows you to create multiple schemas in a database. Schema is equivalent to a group, and you can put different tables under different Schemas according to your needs.

PostgreSQL Create Schema

To create a new schema in the current database, you must have CREATE permission. Use the CREATE SCHEMA statement as follows:

CREATE SCHEMA [IF NOT EXISTS] schema_name
[AUTHORIZATION role_name];

Explanation:

  • The CREATE SCHEMA statement is used to create a new schema in the current database.
  • The schema_name is the name of the schema. It should be unique within a database.
  • The IF NOT EXISTS is optional. It just creates a new schema only if the specified schema name does not exist. If this option is not used and the specified schema exists, PostgreSQL will give an error.
  • The AUTHORIZATION role_name clause is used to create the schema for the specified role/user.

To create a table in the schema, use the CREATE TABLE statement, with the table name in the format schema_name.table_name, as follows:

CREATE TABLE schema_name.table_name
(...)

If you omit the schema name schema_name, the default schema is publish.

If you want to use a table in the schema, the table name is schema_name.table_name in the format, as follows:

SELECT * FROM schema_name.table_name;

PostgreSQL Rename Schemas

If you want to rename an existing schema, use the ALTER SCHEMA statement as follows:

ALTER SCHEMA schema_name
RENAME TO new_name;

Explanation:

  • The schema_name is the name of the schema.
  • The new_name is the new name for the schema.

PostgreSQL Change schema owner

If you want to change the owner of a schema, use the ALTER SCHEMA statement as follows:

ALTER SCHEMA schema_name
OWNER TO { new_owner | CURRENT_USER | SESSION_USER};

Explanation:

  • The schema_name is the name of the schema.
  • The new_owner is the new owner/role of the schema.

PostgreSQL Drop Schemas

If you want to drop a schema, use the DROP SCHEMA statement as follows:

DROP SCHEMA [IF EXISTS] schema_name
[ CASCADE | RESTRICT ];

Explanation:

  • The schema_name is the name of the schema.
  • The IF EXISTS is optional, and it instructs the deletion to be performed only if the specified schema exists, and no error is returned if it does not exist.
  • The RESTRICT option indicates that it can only be dropped if the schema is empty, which is the default. The CASCADE option indicates the deletion of the schema and the objects within it, as well as the objects on which the objects depend.

Conclusion

This article discusses the basic usage of PostgreSQL schemas, including creating schemas, modifying schemas, and dropping schemas.