PostgreSQL - Schema

PostgreSQL is a powerful, open-source relational database management system (RDBMS) that is widely used for managing data. One of the most important concepts in PostgreSQL is the schema. A schema is a way of organizing database objects like tables, views, indexes, functions, and data types.

In this guide, we will explore what a schema in PostgreSQL is, how to create and manage schemas, and how they help us organize and secure our database objects.

What is the PostgreSQL Schema?

In PostgreSQL, a schema is essentially a namespace that contains various database objects. Each schema provides a way to group related objects together, making the database more organized and easier to manage. For example, we can use separate schemas for different application modules, like sales, inventory, and hr.

A single PostgreSQL database can contain multiple schemas. By default, PostgreSQL creates a schema named public in every new database. Any object created without explicitly specifying a schema is placed in the public schema.

Syntax

To access an object within a schema, users need to qualify its name with the schema name as a prefix:

schema_name.object_name

A single database can contain one or multiple schemas while each schema belongs to only one database. This allows different schemas to have objects with the same name without conflict. For example, both the 'sales' and 'public' schemas can have a table named 'staff':

public.staff

or,

sales.staff

Why Use Schemas in PostgreSQL?

Schemas in PostgreSQL are beneficial for a variety of reasons:

1. Organizing Database Objects: Schemas help us logically group related tables and other objects. This is useful when our database has multiple applications, each with its own set of tables.
   
2. Multiple Users: Schemas allow multiple users to work with the same database without conflicting with each other. Users can create their objects in different schemas.
   
3. Namespace Management: Using schemas allows multiple objects with the same name to coexist in a database. For example, you can have a staff table in both the sales and hr schemas.

PostgreSQL Schema Operations

Let’s go through the key operations we can perform with schemas in PostgreSQL.

1. Creating a Schema

To create a new schema, we use the 'CREATE SCHEMA' statement.

CREATE SCHEMA schema_name;

2. Renaming a Schema or Changing Its Owner

To rename a schema or change its owner, we use the 'ALTER SCHEMA' statement.

ALTER SCHEMA schema_name RENAME TO new_schema_name;

ALTER SCHEMA schema_name OWNER TO new_owner;

3. Dropping a Schema

To drop a schema, we use the 'DROP SCHEMA' statement. We can specify 'CASCADE' to drop all objects within the schema or 'RESTRICT' to ensure the schema is only dropped if it is empty:

DROP SCHEMA schema_name CASCADE;

DROP SCHEMA schema_name RESTRICT;

Public schema

By default, PostgreSQL creates a schema named 'public' in every database. Objects created without specifying a schema are automatically placed in the 'public' schema. Therefore, the following statements are equivalent:

CREATE TABLE table_name(...);

and

CREATE TABLE public.table_name(...);

Examples of PostgreSQL Schemas

Let's go through some practical examples of PostgreSQL schemas to better understand their usage.

1. Creating Tables in Different Schemas

When we create tables in a specific schema, we must specify the schema name in the CREATE TABLE statement.

Example: Creating a staff table in the sales schema:

CREATE TABLE sales.staff (
    staff_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
);

Creating a staff table in the hr schema:

CREATE TABLE hr.staff (
    staff_id SERIAL PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
);

Explanation:

Both tables can exist in the same database, even though they have the same name, because they belong to different schemas.

2. Accessing Schema Objects

To access an object in a specific schema, prefix the object name with the schema name.

Example: To select data from the staff table in the sales schema:

SELECT * FROM sales.staff;

To select data from the staff table in the hr schema:

SELECT * FROM hr.staff;

3. Granting Privileges on Schemas

To allow a user to access or modify objects in a specific schema, we must grant appropriate privileges using the GRANT statement.

Example: Granting the USAGE privilege on the sales schema to the user john_doe:

GRANT USAGE ON SCHEMA sales TO john_doe;

Granting the CREATE privilege on the hr schema to the user john_doe:

GRANT CREATE ON SCHEMA hr TO john_doe;

Managing Schema Privileges in PostgreSQL

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, we 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, we 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.

Important Points About PostgreSQL Schema

• Use schemas to logically group related objects. For example, use separate schemas for different modules of an application.
  
• Adopt clear and consistent naming conventions for schemas to improve database organization and readability.
  
• When performing backup and restore operations, ensure that schema structures are correctly maintained.
  
• Document schema structures and their purposes within the database to aid in maintenance and development.

Conclusion

PostgreSQL schemas are a powerful feature that helps us organize our database objects, manage user access, and avoid naming conflicts in larger databases. By using schemas effectively, we can enhance the security, maintainability, and scalability of our database. Understanding how to create, manage, and use schemas is an essential skill for any PostgreSQL user.