PostgreSQL – Schema

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, and operators.
To access an object of a schema, users need to qualify its name with the schema name as a prefix as shown below:

Syntax: schema_name.object_name

A database can contain one or multiple schemas while each schema belongs to only one database. Two schemas can have different objects that share the same name. For instance, you may have a sales schema that has a staff table and the public schema which also has the staff table. When you refer to the staff table you must qualify it as follows:

public.staff

or,

sales.staff

There are various scenario where schemas need to be used:

• Schemas allow users to organize database objects e.g., tables into logical groups to make them more manageable.
• Schemas enable multiple users to use one database without interfering with each other.

Public schema

PostgreSQL generates a schema named public for all databases. Whatever object users create without specifying the schema name, PostgreSQL will automatically put it into this public schema. Therefore, the following statements are equal:

CREATE TABLE table_name(...);

and

CREATE TABLE public.table_name(...);

Schemas and Privileges

Users can only access objects in the schemas that they own. It means they cannot access any object in the schemas, which does not belong to them. To enable users to access the objects in the schema that they do not own, you must grant the USAGE privilege to the users on the schema as shown in the following statement:

GRANT USAGE ON SCHEMA schema_name TO user_name;

To allow users to create objects in the schema that they do not own, you need to grant them the CREATE privilege on the schema.

GRANT CREATE ON SCHEMA schema_name TO user_name;

Note: By default, every user has the CREATE and USAGE on the public schema.

Schema Operations

The below operations is allowed by PostgreSQL:

• To create a new schema, you use the CREATE SCHEMA statement.
• To rename a schema or change its owner, you use the ALTER SCHEMA statement.
• To drop a schema, you use the DROP SCHEMA statement.